PG Extract Function Example

PG processing time is very flexible, +-*/both have support

It's useful to have a extract function in the meantime, let's take a look at a few examples: [code]

postgres=# Select Extract (Epoch from ' 1970-01-01 ':: timestamp);
Date_part
-----------
0
(1 row)

postgres=# Select Extract (Epoch from ' 1970-01-01 00:00:01 ':: timestamp);
Date_part
-----------
1
(1 row)

postgres=# Select Extract (Epoch from now ()-' 2013-07-01 ':: timestamp);
Date_part
----------------
1936767.072764
(1 row)

postgres=#
[/code] The above example is to find out the number of seconds starting from 1970-01-01 00:00:00

The function of the extract function is to extract the corresponding fields from the time

His use of the format:
EXTRACT (field from source)

Where field contains the following values:

Century: Century

postgres=# Select Extract (Century from ' 2013-07-01 '::d ate);
Date_part
-----------
21st
(1 row)

Day: The first days of one months [code]

postgres=# Select Extract (Day from ' 2013-07-23 '::d ate);
Date_part
-----------
23
(1 row)

postgres=# Select Extract (Day from ' 2013-07-23 09:15:23 ':: timestamp);
Date_part
-----------
23
(1 row)

postgres=# Select Extract (Day from ' 2013-07-23 09:15:23 '::d ate);
Date_part
-----------
23
(1 row)

postgres=# Select Extract (Day from interval ' 3 hours ');
Date_part
-----------
40
(1 row)


[/code]Decade:10, number of years 10



postgres=# Select Extract (decade from ' 2013-07-23 09:15:23 '::d ate);
Date_part
-----------
201
(1 row)


Dow Day of the week (Sunday =0 saturday=6)


postgres=# Select Extract (Dow from ' 2013-07-23 09:15:23 '::d ate);
Date_part
-----------
2
(1 row)

postgres=# Select Extract (Dow from ' 2013-07-21 09:15:23 '::d ate);
Date_part
-----------
0
(1 row)


Doy: The first day of the year(1-365/366)

postgres=# Select Extract (Doy from ' 2013-07-21 09:15:23 '::d ate);
Date_part
-----------
202
(1 row)

Hour: Hours of the day (0-23)

postgres=# SELECT EXTRACT (HOUR from TIMESTAMP ' 2013-07-21 09:15:23 ');
Date_part
-----------
9
(1 row)

postgres=# Select Extract (Hour from ' 2013-07-21 09:15:23 '::d ate);
Date_part
-----------
0
(1 row)

Notice here, because we have the ' 2013-07-21 09:15:23 '::d ate) to the date type is no hour, so return 0, the upper timestamp is an hour, correctly returned


Isodow:iso standard Days of the week sunday=7 Monday=1

postgres=# Select Extract (Isodow from ' 2013-07-21 09:15:23 '::d ate);
Date_part
-----------
7
(1 row)

postgres=# Select Extract (Dow from ' 2013-07-21 09:15:23 '::d ate);
Date_part
-----------
0
(1 row)

postgres=# Select Extract (Isodow from ' 2013-07-21 09:15:23 ':: timestamp);
Date_part
-----------
7
(1 row)

Year of the Isoyear:iso Standard: (

The ISO standard year is starting from Monday, with the beginning of the new Year of Monday before January 4, which differs from the A.D., so the first few days of January, or the days after December, may differ from the A.D.:


postgres=# Select Extract (isoyear from ' 2013-01-01 '::d ate);
Date_part
-----------
2013
(1 row)

postgres=# Select Extract (isoyear from ' 2012-12-31 '::d ate);
Date_part
-----------
2013
(1 row)

postgres=# Select Extract (isoyear from ' 2012-12-30 '::d ate);
Date_part
-----------
2012
(1 row)

postgres=# Select Extract (Dow from ' 2012-12-31 '::d ate);
Date_part
-----------
1
(1 row)


microseconds: microseconds

The part of the second that is identified in microseconds, including the following fractional parts:

postgres=# Select Extract (microseconds from interval ' 3 days 5 mins 3.5 sec ');
Date_part
-----------
3500000
(1 row)

postgres=# Select Extract (microseconds from ' 2013-07-21 09:15:23 ':: timestamp)
postgres-#;
Date_part
-----------
23000000
(1 row)



Millennium: Millennium, Millennium Year

It is 21st century, the 3rd millennium

postgres=# Select Extract (Millennium from ' 2013-07-21 09:15:23 ':: timestamp);
Date_part
-----------
3
(1 row)


minute: minutes (0-59)

postgres=# Select extract (minute from ' 2013-07-21 09:15:23 ':: timestamp);
Date_part
-----------
15
(1 row)



Month : Returns 1-12 for the timestamp type, and returns 0-11 for the interval type

postgres=# Select Extract (month from ' 2013-07-21 09:15:23 ':: timestamp);
Date_part
-----------
7
(1 row)

postgres=# Select Extract (Month from interval ' 7 months 5 days ');
Date_part
-----------
7
(1 row)


postgres=# Select Extract (Month from interval ' 5 days ');
Date_part
-----------
0
(1 row)

postgres=# Select Extract (month from interval ' months 5 days ');
Date_part
-----------
0
(1 row)

postgres=# Select Extract (month from interval ' One months 5 days ');
Date_part
-----------
11
(1 row)


Quarter: Quarterly

postgres=# Select Extract (Quarter from ' 2013-07-21 09:15:23 ':: timestamp);
Date_part
-----------
3
(1 row)

postgres=# Select Extract (Quarter from ' 2013-06-21 09:15:23 ':: timestamp);
Date_part
-----------
2
(1 row)


Second: seconds (0-59)

postgres=# Select Extract (second from ' 2013-06-21 09:15:23 ':: timestamp);
Date_part
-----------
23
(1 row)


Week: Zhou Kee


postgres=# Select Extract (Week from ' 2013-06-21 09:15:23 ':: timestamp);
Date_part
-----------
25
(1 row)


Year : Age



postgres=# Select Extract (Year from ' 2013-06-21 09:15:23 ':: timestamp);
Date_part
-----------
2013
(1 row)

PG Extract Function Example