PostgreSQL EXTRACT
From w3cyberlearnings
Contents |
Postgresql extract
The function extracts subfields such as year or hour from date/time values. The Extract() function is similar to date_part()
Syntax extract
- returnfield: specifies the return field.
- expre: is timestamp or interval expression of date or datetime.
extract(returnfield from 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 extract(DAY FROM TIMESTAMP '2012-04-13 20:40:50');
date_part
-----------
13
(1 row)
postgres=# SELECT extract(YEAR FROM TIMESTAMP '2012-04-13 20:40:50');
date_part
-----------
2012
(1 row
Example 2
postgres=# SELECT extract(DAY FROM INTERVAL '30 years 50 days');
date_part
-----------
50
(1 row)
postgres=# SELECT extract(YEAR FROM INTERVAL '2 years 3 days');
date_part
-----------
2
(1 row)
Example 3
postgres=# SELECT name, age, extract(year from age) FROM employee; name | age | date_part --------+------------+----------- John | 1990-04-13 | 1990 Week | 1991-06-12 | 1991 Christ | 1995-05-17 | 1995 (3 rows)
Related Links
- date_part
- date_trunc
- extract
- isfinite
- justify_days
