Table of Contents
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.
|
1 2 |
select to_char(current_timestamp, 'YYYY-MM-DD HH12:MI:SS'); --> '2018-01-04 10:04:17' |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
------------------------------ -- use current_date ------------------------------ select date_part('year', current_date); --> 2018 select date_part('month', current_date); --> 1 select date_part('day', current_date); --> 4 ------------------------------ -- use current_timestamp ------------------------------ select date_part('year', current_timestamp); --> 2018 select date_part('month', current_timestamp); --> 1 select date_part('day', current_timestamp); --> 4 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
------------------------------ -- use current_time ------------------------------ select date_part('hour', current_time); --> 20 select date_part('minute', current_time); --> 54 select date_part('second', current_time); --> 35.144505 ------------------------------ -- use current_timestamp ------------------------------ select date_part('hour', current_timestamp); --> 20 select date_part('minute', current_timestamp); --> 54 select date_part('second', current_timestamp); --> 35.144505 |
