PostgreSQL DATE PART
From w3cyberlearnings
Contents |
Postgresql date_part
This functions get subfield of given timestamp or interval.
Syntax date_part
- returnfield:specifies what is the return field
- expres: date, datetime, or date interval values
date_part(returnfield, expres);
returnfield:
- century: return the century
- day: the number of days
- decade: the year field divided by 10
- dow: day of week (Sunday=0 to Saturday=6)
- doy: the day of the year (1-365 or 366)
- epoch:For date and timestamp values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for interval values, the total number of seconds in the interval
- hour:The hour field (0 - 23)
- isodow: The day of the week (Monday=1 to Sunday=7)
- isoyear: The ISO 8601 year that the date fall in.
- microseconds:The seconds field, including fractional parts, multiplied by 1 000 000; note that this includes full seconds
- millennium: Years in the 1900s are in the second millennium. The third millennium started January 1, 2001.
- milliseconds: The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.
- minute:The minutes field (0 - 59)
- month: The month (0-11)
- quarter: The quarter of the year (1-4)
- second: The seconds field, including fractional parts (0 - 59)
- timezone:The time zone offset from UTC, measured in seconds. Positive values correspond to time zones east of UTC, negative values to zones west of UTC. (Technically, PostgreSQL uses UT1 because leap seconds are not handled.)
- timezone_hour:The hour component of the time zone offset
- timezone_minute:The minute component of the time zone offset
- week:The number of the week of the year that the day is in
- year:The year field.
Example 1
postgres=# SELECT date_part('hour', timestamp '2012-05-24 10:40:50');
date_part
-----------
10
(1 row)
postgres=# SELECT date_part('year', timestamp '2012-05-24 10:40:50');
date_part
-----------
2012
(1 row)
postgres=# SELECT date_part('second', timestamp '2012-05-24 10:40:50');
date_part
-----------
50
(1 row)
Example 2
postgres=# SELECT date_part('month', interval '3 years 30 months 40 days');
date_part
-----------
6
(1 row)
postgres=# SELECT date_part('days', interval '3 years 30 months 40 days');
date_part
-----------
40
(1 row)
postgres=# SELECT date_part('year', interval '3 years 30 months 40 days');
date_part
-----------
5
(1 row)
Example 3
postgres=# SELECT name, age, date_part('month',age) as month
postgres-# FROM employee;
name | age | month
--------+------------+-------
John | 1990-04-13 | 4
Week | 1991-06-12 | 6
Christ | 1995-05-17 | 5
(3 rows)
Related Links
- date_part
- date_trunc
- extract
- isfinite
- justify_days
