Reputation: 24508
timestamp with timezone is this - 2020-05-31T10:05:07Z
this is not working, despite referencing official documentation. I need to extract may 2020 or separate month and year to compare against May 2020
SELECT date_trunc('hour', TIMESTAMP '2020-05-31T10:05:07Z')
SELECT date_part('day', TIMESTAMP '2020-05-31T10:05:07Z');
Upvotes: 0
Views: 3842
Reputation: 23766
You need to_char()
to format a date or timestamp. Mon
gives you the first three letters of a month name:
SELECT
to_char(
TIMESTAMP '2020-05-31T10:05:07Z',
'Mon YYYY'
)
Returning the entire month name you can use Month
instead of Mon
. But, for some reasons, the length of the Month
value is fixed to the longest month name available. That means May
is returned with right padded spaces. To avoid this, you need to add the modifier FM
:
SELECT
to_char(
TIMESTAMP '2020-05-31T10:05:07Z',
'FMMonth YYYY'
)
Upvotes: 0
Reputation:
If you want to check if a timestamp value is "may 2020", you have different options.
to_char(the_value, 'yyyy-mm') = '2020-05'
or
extract(month from the_value) = 5
and extract(year from the_value) = 2020
or
(extract(month from the_value), extract(year from the_value)) = (5, 2020)
extract()
and date_part()
are the same thing - but I prefer the standard compliant extract()
version.
Upvotes: 1