PostgreSQL: How to Get Current Year, Month, Day, Hour, Minute and Second


Here is the way to get current year, month, day, hour, minute and second in PostgreSQL.

Environment

  • PostgreSQL 9.5

Get Year, Month, Day, Hour, Minute, Second as Character

To get year, month, day, hour, minute, second as character, use to_char and current_timestamp (or current_date, current_time). Here is an example to use current_timestamp.

Other display formats are in PostgreSQL document. There are many formats.

If we use to_char and to_number, we can get year, month, day, hour, minute and second also as number. But PostgreSQL has the function to get them as number at once. I will introduce it.

Get Year, Month, Day as Number

Use current_date (or current_timestamp) and date_part. date_part returns number of year, month and day.

Get Hour, Minute and Second as Number

Use current_time (or current_timestamp) and date_part. date_part returns the number of hour, minute and second.