更新时间:2024-09-02 GMT+08:00

EXTRACT

EXTRACT(field FROM source)

extract函数从日期或时间的数值里抽取子域,比如年、小时等。source必须是一个timestamp、time或interval类型的值表达式(类型为date的表达式转换为timestamp,因此也可以用)。field是一个标识符或者字符串,它指定从源数据中抽取的域。extract函数返回类型为double precision的数值。field的取值范围如下所示。

century

世纪。

第一个世纪从0001-01-01 00:00:00 AD开始。这个定义适用于所有使用阳历的国家。没有0世纪,直接从公元前1世纪到公元1世纪。

示例:

1
2
3
4
5
SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13');
 date_part 
-----------
        20
(1 row)

day

  • 如果source为timestamp,表示月份里的日期(1-31)。
    1
    2
    3
    4
    5
    SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40');
     date_part 
    -----------
            16
    (1 row)
    
  • 如果source为interval,表示天数。
    1
    2
    3
    4
    5
    SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
     date_part 
    -----------
            40
    (1 row)
    

decade

年份除以10。

1
2
3
4
5
SELECT EXTRACT(DECADE FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part 
-----------
       200
(1 row)

dow

每周的星期几,星期天(0)到星期六(6)。

1
2
3
4
5
SELECT EXTRACT(DOW FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part 
-----------
         5
(1 row)

doy

一年的第几天(1~365/366)。

1
2
3
4
5
SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part 
-----------
        47
(1 row)

epoch

  • 如果source为timestamp with time zone,表示自1970-01-01 00:00:00-00 UTC以来的秒数(结果可能是负数);

    如果source为date和timestamp,表示自1970-01-01 00:00:00-00当地时间以来的秒数;

    如果source为interval,表示时间间隔的总秒数。

    1
    2
    3
    4
    5
    SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40.12-08');
      date_part   
    --------------
     982384720.12
    (1 row)
    
    1
    2
    3
    4
    5
    SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
     date_part 
    -----------
        442800
    (1 row)
    
  • 将epoch值转换为时间戳的方法。
    1
    2
    3
    4
    5
    SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second' AS RESULT;
              result          
    ---------------------------
     2001-02-17 12:38:40.12+08
    (1 row)
    

hour

小时域(0-23)。

1
2
3
4
5
SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part 
-----------
        20
(1 row)

isodow

一周的第几天(1-7)。

星期一为1,星期天为7。

除了星期天外,都与dow相同。

1
2
3
4
5
SELECT EXTRACT(ISODOW FROM TIMESTAMP '2001-02-18 20:38:40');
 date_part 
-----------
         7
(1 row)

isoyear

日期中的ISO 8601标准年(不适用于间隔)。

每个带有星期一开始的周中包含1月4日的ISO年,所以在年初的1月或12月下旬的ISO年可能会不同于阳历的年。详细信息请参见后续的week描述。

1
2
3
4
5
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-01');
 date_part 
-----------
      2005
(1 row)
1
2
3
4
5
SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
 date_part 
-----------
      2006
(1 row)

microseconds

秒域(包括小数部分)乘以1,000,000。

1
2
3
4
5
SELECT EXTRACT(MICROSECONDS FROM TIME '17:12:28.5');
 date_part 
-----------
  28500000
(1 row)

millennium

千年。

20世纪(19xx年)里面的年份在第二个千年里。第三个千年从2001年1月1日零时开始。

1
2
3
4
5
SELECT EXTRACT(MILLENNIUM FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part 
-----------
         3
(1 row)

milliseconds

秒域(包括小数部分)乘以1000。请注意它包括完整的秒。

1
2
3
4
5
SELECT EXTRACT(MILLISECONDS FROM TIME '17:12:28.5');
 date_part 
-----------
     28500
(1 row)

minute

分钟域(0-59)。

1
2
3
4
5
SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part 
-----------
        38
(1 row)

month

如果source为timestamp,表示一年里的月份数(1-12)。

1
2
3
4
5
SELECT EXTRACT(MONTH FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part 
-----------
         2
(1 row)

如果source为interval,表示月的数目,然后对12取模(0-11)。

1
2
3
4
5
SELECT EXTRACT(MONTH FROM INTERVAL '2 years 13 months');
 date_part 
-----------
         1
(1 row)

quarter

该天所在的该年的季度(1-4)。

1
2
3
4
5
SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part 
-----------
         1
(1 row)

second

秒域,包括小数部分(0-59)。

1
2
3
4
5
SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
 date_part 
-----------
      28.5
(1 row)

timezone

与UTC的时区偏移量,单位为秒。正数对应UTC东边的时区,负数对应UTC西边的时区。

1
2
3
4
5
SELECT EXTRACT(timezone FROM TIMETZ '17:12:28');
 date_part
-----------
   0
(1 row)

timezone_hour

时区偏移量的小时部分。

1
2
3
4
5
SELECT EXTRACT(timezone_hour FROM TIMETZ '17:12:28');
 date_part
-----------
       0
(1 row)

timezone_minute

时区偏移量的分钟部分。

1
2
3
4
5
SELECT EXTRACT(timezone_minute FROM TIMETZ '17:12:28');
 date_part
-----------
         0
(1 row)

week

该天在所在的年份里是第几周。ISO 8601定义一年的第一周包含该年的一月四日(ISO-8601 的周从星期一开始)。换句话说,一年的第一个星期四在第一周。

在ISO定义里,一月的头几天可能是前一年的第52或者第53周,十二月的后几天可能是下一年第一周。比如,2005-01-01是2004年的第53周,而2006-01-01是2005年的第52周,2012-12-31是2013年的第一周。建议isoyear字段和week一起使用以得到一致的结果。

1
2
3
4
5
SELECT EXTRACT(WEEK FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part 
-----------
         7
(1 row)

year

年份域。

1
2
3
4
5
SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
 date_part 
-----------
      2001
(1 row)