AWS Athena and date_format

Question

I have some issue while formatting a timestamp with Amazon Athena service.

select date_format(current_timestamp, 'y')

Returns just 'y' (the string).

The only way I found to format dates in Amazon Athena is trough CONCAT + YEAR + MONTH + DAY functions, like this:

select CONCAT(cast(year(current_timestamp) as varchar), '_', cast(day(current_timestamp) as varchar))

It is not clear what is your required result. Please add a data sample. — David דודו Markovitz, May 19 '17 at 08:32

score 31 · Accepted Answer · answered May 19 '17 at 08:47

31

select  current_timestamp

       ,date_format     (current_timestamp, '%Y_%m_%d')
       ,format_datetime (current_timestamp, 'y_M_d')
;

+---------------------+------------+-----------+
|        _col0        |   _col1    |   _col2   |
+---------------------+------------+-----------+
| 2017-05-19 14:46:12 | 2017_05_19 | 2017_5_19 |
+---------------------+------------+-----------+

https://prestodb.io/docs/current/functions/datetime.html

answered May 19 '17 at 08:47

David דודו Markovitz

42,900
6
64
88

I was able to get this to work using David's second function, removing the `%`s, not the first. – Josh Herzberg May 18 '21 at 21:27

score 0 · Answer 2 · answered Nov 14 '22 at 00:30

0

This will work as well with js, if it helps anyone...

const current = new Date();
date: `${current.getFullYear()}-${current.getMonth() + 1}-${current.getDate()}`

answered Nov 14 '22 at 00:30

DoGzTheFiGhTeR

296
2
9

AWS Athena and date_format

2 Answers2

Linked