CODEWITHSUNDEEP
postgresqltimestamp
ERJAN
ERJAN

Reputation: 24508

how to get hour, month from timestamp in postgresql

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

Answers (2)

S-Man
S-Man

Reputation: 23766

demo:db<>fiddle

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

user330315
user330315

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

Related Questions