PG Extract Function Example

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.