« 分组函数 全文搜索函数 »
Elastic 文档 ›Elasticsearch 指南 [8.16] ›SQL ›函数和运算符

日期/时间和间隔函数及运算符

编辑

日期/时间和间隔函数及运算符

编辑

Elasticsearch SQL 提供了广泛的功能来执行日期/时间操作。

间隔

编辑

处理日期/时间时的一个常见需求围绕着interval（间隔）的概念，这是一个值得在 Elasticsearch 和 Elasticsearch SQL 上下文中探讨的主题。

Elasticsearch 广泛支持日期数学，包括在索引名称和查询中。在 Elasticsearch SQL 中，前者通过在表名中传递表达式来支持，而后者则通过标准 SQL INTERVAL 来支持。

下表显示了 Elasticsearch 和 Elasticsearch SQL 之间的映射

Elasticsearch

Elasticsearch SQL

索引/表日期时间数学

<index-{now/M{YYYY.MM}}>

查询日期/时间数学

1y

INTERVAL 1 YEAR

2M

INTERVAL 2 MONTH

3w

INTERVAL 21 DAY

4d

INTERVAL 4 DAY

5h

INTERVAL 5 HOUR

6m

INTERVAL 6 MINUTE

7s

INTERVAL 7 SECOND

INTERVAL 允许混合使用 YEAR 和 MONTH，或者 DAY、HOUR、MINUTE 和 SECOND。

Elasticsearch SQL 也接受每个时间单位的复数形式（例如，YEAR 和 YEARS 都有效）。

以下是可能的组合示例

间隔

描述

INTERVAL '1-2' YEAR TO MONTH

1 年 2 个月

INTERVAL '3 4' DAYS TO HOURS

3 天 4 小时

INTERVAL '5 6:12' DAYS TO MINUTES

5 天 6 小时 12 分钟

INTERVAL '3 4:56:01' DAY TO SECOND

3 天 4 小时 56 分钟 1 秒

INTERVAL '2 3:45:01.23456789' DAY TO SECOND

2 天 3 小时 45 分钟 1 秒 234567890 纳秒

INTERVAL '123:45' HOUR TO MINUTES

123 小时 45 分钟

INTERVAL '65:43:21.0123' HOUR TO SECONDS

65 小时 43 分钟 21 秒 12300000 纳秒

INTERVAL '45:01.23' MINUTES TO SECONDS

45 分钟 1 秒 230000000 纳秒

比较

编辑

日期/时间字段可以使用等于运算符 (=) 和 IN 运算符与日期数学表达式进行比较。

SELECT hire_date FROM emp WHERE hire_date = '1987-03-01||+4y/y';

       hire_date
------------------------
1991-01-26T00:00:00.000Z
1991-10-22T00:00:00.000Z
1991-09-01T00:00:00.000Z
1991-06-26T00:00:00.000Z
1991-08-30T00:00:00.000Z
1991-12-01T00:00:00.000Z
SELECT hire_date FROM emp WHERE hire_date IN ('1987-03-01||+2y/M', '1987-03-01||+3y/M');

       hire_date
------------------------
1989-03-31T00:00:00.000Z
1990-03-02T00:00:00.000Z

运算符

编辑

基本算术运算符 (+、-、*) 支持如下所示的日期/时间参数。

SELECT INTERVAL 1 DAY + INTERVAL 53 MINUTES AS result;

    result
---------------
+1 00:53:00
SELECT CAST('1969-05-13T12:34:56' AS DATETIME) + INTERVAL 49 YEARS AS result;

       result
--------------------
2018-05-13T12:34:56Z
SELECT - INTERVAL '49-1' YEAR TO MONTH result;

    result
---------------
-49-1
SELECT INTERVAL '1' DAY - INTERVAL '2' HOURS AS result;

    result
---------------
+0 22:00:00
SELECT CAST('2018-05-13T12:34:56' AS DATETIME) - INTERVAL '2-8' YEAR TO MONTH AS result;

       result
--------------------
2015-09-13T12:34:56Z
SELECT -2 * INTERVAL '3' YEARS AS result;

    result
---------------
-6-0

函数

编辑

针对日期/时间的函数。

CURRENT_DATE/CURDATE

编辑

概要

CURRENT_DATE
CURRENT_DATE()
CURDATE()

输入：无

输出：日期

描述：返回当前查询到达服务器时的日期（无时间部分）。它既可以用作关键字：CURRENT_DATE，也可以用作没有参数的函数：CURRENT_DATE()。

与 CURRENT_DATE 不同，CURDATE() 只能用作没有参数的函数，而不能用作关键字。

此方法在其在同一查询中的每次出现时始终返回相同的值。

SELECT CURRENT_DATE AS result;

         result
------------------------
2018-12-12
SELECT CURRENT_DATE() AS result;

         result
------------------------
2018-12-12
SELECT CURDATE() AS result;

         result
------------------------
2018-12-12

通常，此函数（及其孪生函数TODAY()）用于相对日期过滤。

SELECT first_name FROM emp WHERE hire_date > TODAY() - INTERVAL 35 YEARS ORDER BY first_name ASC LIMIT 5;

 first_name
------------
Alejandro
Amabile
Anoosh
Basil
Bojan

CURRENT_TIME/CURTIME

编辑

概要

CURRENT_TIME
CURRENT_TIME([precision]) 
CURTIME

输入:

小数位数；可选

输出：时间

描述：返回当前查询到达服务器时的时间。CURRENT_TIME() 函数接受 *precision* 作为可选参数，用于四舍五入秒的小数位数（纳秒）。默认 *precision* 为 3，这意味着将返回毫秒精度的当前时间。

此方法在其在同一查询中的每次出现时始终返回相同的值。

SELECT CURRENT_TIME AS result;

         result
------------------------
12:31:27.237Z
SELECT CURRENT_TIME() AS result;

         result
------------------------
12:31:27.237Z
SELECT CURTIME() AS result;

         result
------------------------
12:31:27.237Z
SELECT CURRENT_TIME(1) AS result;

         result
------------------------
12:31:27.2Z

通常，此函数用于相对日期/时间过滤。

SELECT first_name FROM emp WHERE CAST(hire_date AS TIME) > CURRENT_TIME() - INTERVAL 20 MINUTES ORDER BY first_name ASC LIMIT 5;

  first_name
---------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam

目前，使用大于 6 的 *precision* 不会对函数的输出产生任何影响，因为返回的秒小数位数最大为 6。

CURRENT_TIMESTAMP

编辑

概要

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])

输入:

小数位数；可选

输出：日期/时间

描述：返回当前查询到达服务器时的日期/时间。CURRENT_TIMESTAMP() 函数接受 *precision* 作为可选参数，用于四舍五入秒的小数位数（纳秒）。默认 *precision* 为 3，这意味着将返回毫秒精度的当前日期/时间。

此方法在其在同一查询中的每次出现时始终返回相同的值。

SELECT CURRENT_TIMESTAMP AS result;

         result
------------------------
2018-12-12T14:48:52.448Z
SELECT CURRENT_TIMESTAMP() AS result;

         result
------------------------
2018-12-12T14:48:52.448Z
SELECT CURRENT_TIMESTAMP(1) AS result;

         result
------------------------
2018-12-12T14:48:52.4Z

通常，此函数（及其孪生函数NOW()）用于相对日期/时间过滤。

SELECT first_name FROM emp WHERE hire_date > NOW() - INTERVAL 100 YEARS ORDER BY first_name ASC LIMIT 5;

  first_name
---------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam

目前，使用大于 6 的 *precision* 不会对函数的输出产生任何影响，因为返回的秒小数位数最大为 6。

DATE_ADD/DATEADD/TIMESTAMP_ADD/TIMESTAMPADD

编辑

概要

DATE_ADD(
    string_exp, 
    integer_exp, 
    datetime_exp)

输入:

表示要添加到日期/日期时间的日期/时间单位的字符串表达式。如果为 null，则函数返回 null。

表示应将上述单位添加到/从日期/日期时间中多少次的整数表达式，如果使用负值，则会导致从日期/日期时间中减去。如果为 null，则函数返回 null。

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：日期时间

描述：将给定数量的日期/时间单位添加到日期/日期时间。如果单位数为负数，则将其从日期/日期时间中减去。

如果第二个参数是长整型，则可能发生截断，因为将从该长整型中提取并使用整数值。

要添加/减去的日期时间单位

单位

缩写

年

years, yy, yyyy

季度

quarters, qq, q

月

months, mm, m

一年中的第几天

dy, y

日

days, dd, d

周

weeks, wk, ww

工作日

weekdays, dw

小时

hours, hh

分钟

minutes, mi, n

秒

seconds, ss, s

毫秒

milliseconds, ms

微秒

microseconds, mcs

纳秒

nanoseconds, ns

 
SELECT DATE_ADD('years', 10, '2019-09-04T11:22:33.000Z'::datetime) AS "+10 years";

      +10 years
------------------------
2029-09-04T11:22:33.000Z
SELECT DATE_ADD('week', 10, '2019-09-04T11:22:33.000Z'::datetime) AS "+10 weeks";

      +10 weeks
------------------------
2019-11-13T11:22:33.000Z
SELECT DATE_ADD('seconds', -1234, '2019-09-04T11:22:33.000Z'::datetime) AS "-1234 seconds";

      -1234 seconds
------------------------
2019-09-04T11:01:59.000Z
SELECT DATE_ADD('qq', -417, '2019-09-04'::date) AS "-417 quarters";

      -417 quarters
------------------------
1915-06-04T00:00:00.000Z
SELECT DATE_ADD('minutes', 9235, '2019-09-04'::date) AS "+9235 minutes";

      +9235 minutes
------------------------
2019-09-10T09:55:00.000Z

DATE_DIFF/DATEDIFF/TIMESTAMP_DIFF/TIMESTAMPDIFF

编辑

概要

DATE_DIFF(
    string_exp, 
    datetime_exp, 
    datetime_exp)

输入:

表示以下两个日期/日期时间表达式之间日期/时间单位差的字符串表达式。如果为 null，则函数返回 null。

起始日期/日期时间表达式。如果为 null，则函数返回 null。

结束日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：从第三个参数中减去第二个参数，并以第一个参数中指定的单位的倍数返回它们的差值。如果第二个参数（开始）大于第三个参数（结束），则返回负值。

日期时间差单位

单位

缩写

年

years, yy, yyyy

季度

quarters, qq, q

月

months, mm, m

一年中的第几天

dy, y

日

days, dd, d

周

weeks, wk, ww

工作日

weekdays, dw

小时

hours, hh

分钟

minutes, mi, n

秒

seconds, ss, s

毫秒

milliseconds, ms

微秒

microseconds, mcs

纳秒

nanoseconds, ns

 
SELECT DATE_DIFF('years', '2019-09-04T11:22:33.000Z'::datetime, '2032-09-04T22:33:11.000Z'::datetime) AS "diffInYears";

      diffInYears
------------------------
13
SELECT DATE_DIFF('week', '2019-09-04T11:22:33.000Z'::datetime, '2016-12-08T22:33:11.000Z'::datetime) AS "diffInWeeks";

      diffInWeeks
------------------------
-143
SELECT DATE_DIFF('seconds', '2019-09-04T11:22:33.123Z'::datetime, '2019-07-12T22:33:11.321Z'::datetime) AS "diffInSeconds";

      diffInSeconds
------------------------
-4625362
SELECT DATE_DIFF('qq', '2019-09-04'::date, '2025-04-25'::date) AS "diffInQuarters";

      diffInQuarters
------------------------
23

对于 hour 和 minute，DATEDIFF 不会进行任何舍入，而是首先将两个日期上更详细的时间字段截断为零，然后计算减法。

SELECT DATEDIFF('hours', '2019-11-10T12:10:00.000Z'::datetime, '2019-11-10T23:59:59.999Z'::datetime) AS "diffInHours";

      diffInHours
------------------------
11
SELECT DATEDIFF('minute', '2019-11-10T12:10:00.000Z'::datetime, '2019-11-10T12:15:59.999Z'::datetime) AS "diffInMinutes";

      diffInMinutes
------------------------
5
SELECT DATE_DIFF('minutes', '2019-09-04'::date, '2015-08-17T22:33:11.567Z'::datetime) AS "diffInMinutes";

      diffInMinutes
------------------------
-2128407

DATE_FORMAT

编辑

概要

DATE_FORMAT(
    date_exp/datetime_exp/time_exp, 
    string_exp)

输入:

日期/日期时间/时间表达式。如果为 null，则函数返回 null。

格式模式。如果为 null 或空字符串，则函数返回 null。

输出：字符串

描述：使用第二个参数中指定的格式将日期/日期时间/时间作为字符串返回。格式模式是MySQL DATE_FORMAT() 函数中使用的说明符之一。

如果第一个参数的类型为 time，则第二个参数指定的模式不能包含日期相关的单位（例如，dd、MM、yyyy 等）。如果包含此类单位，则会返回错误。月份和日期说明符 (%c、%D、%d、%e、%m) 的范围从一开始，与 MySQL 不同，MySQL 的范围从零开始，因为 MySQL 允许存储不完整的日期，例如 2014-00-00。Elasticsearch 在这种情况下会返回错误。

SELECT DATE_FORMAT(CAST('2020-04-05' AS DATE), '%d/%m/%Y') AS "date";

      date
------------------
05/04/2020
SELECT DATE_FORMAT(CAST('2020-04-05T11:22:33.987654' AS DATETIME), '%d/%m/%Y %H:%i:%s.%f') AS "datetime";

      datetime
------------------
05/04/2020 11:22:33.987654
SELECT DATE_FORMAT(CAST('23:22:33.987' AS TIME), '%H %i %s.%f') AS "time";

      time
------------------
23 22 33.987000

DATE_PARSE

编辑

概要

DATE_PARSE(
    string_exp, 
    string_exp)

输入:

作为字符串的日期表达式。如果为 null 或空字符串，则函数返回 null。

解析模式。如果为 null 或空字符串，则函数返回 null。

输出：日期

描述：通过使用第二个参数中指定的格式解析第一个参数来返回日期。使用的解析格式模式来自java.time.format.DateTimeFormatter。

如果解析模式不包含所有有效的日期单位（例如，HH:mm:ss、dd-MM HH:mm:ss 等），则会返回错误，因为函数需要返回 date 类型的 value，其中将包含日期部分。

SELECT DATE_PARSE('07/04/2020', 'dd/MM/yyyy') AS "date";

   date
-----------
2020-04-07

生成的date将具有用户通过time_zone/timezone REST/驱动程序参数指定的时区，无需进行任何转换。

{
    "query" : "SELECT DATE_PARSE('07/04/2020', 'dd/MM/yyyy') AS \"date\"",
    "time_zone" : "Europe/Athens"
}

   date
------------
2020-04-07T00:00:00.000+03:00

DATETIME_FORMAT

编辑

概要

DATETIME_FORMAT(
    date_exp/datetime_exp/time_exp, 
    string_exp)

输入:

日期/日期时间/时间表达式。如果为 null，则函数返回 null。

格式模式。如果为 null 或空字符串，则函数返回 null。

输出：字符串

描述：使用第二个参数中指定的格式将日期/日期时间/时间作为字符串返回。使用的格式模式来自java.time.format.DateTimeFormatter。

如果第一个参数的类型为time，则第二个参数中指定的模式不能包含与日期相关的单位（例如dd、MM、yyyy等）。如果包含此类单位，则会返回错误。

SELECT DATETIME_FORMAT(CAST('2020-04-05' AS DATE), 'dd/MM/yyyy') AS "date";

      date
------------------
05/04/2020
SELECT DATETIME_FORMAT(CAST('2020-04-05T11:22:33.987654' AS DATETIME), 'dd/MM/yyyy HH:mm:ss.SS') AS "datetime";

      datetime
------------------
05/04/2020 11:22:33.98
SELECT DATETIME_FORMAT(CAST('11:22:33.987' AS TIME), 'HH mm ss.S') AS "time";

      time
------------------
11 22 33.9

DATETIME_PARSE

编辑

概要

DATETIME_PARSE(
    string_exp, 
    string_exp)

输入:

日期时间表达式作为字符串。如果为null或空字符串，则函数返回null。

解析模式。如果为 null 或空字符串，则函数返回 null。

输出：日期时间

描述：使用第二个参数中指定的格式解析第一个参数，返回日期时间。使用的解析格式模式来自java.time.format.DateTimeFormatter。

如果解析模式仅包含日期单位或仅包含时间单位（例如dd/MM/yyyy、HH:mm:ss等），则会返回错误，因为函数需要返回datetime类型的值，该值必须同时包含日期和时间。

SELECT DATETIME_PARSE('07/04/2020 10:20:30.123', 'dd/MM/yyyy HH:mm:ss.SSS') AS "datetime";

      datetime
------------------------
2020-04-07T10:20:30.123Z
SELECT DATETIME_PARSE('10:20:30 07/04/2020 Europe/Berlin', 'HH:mm:ss dd/MM/yyyy VV') AS "datetime";

      datetime
------------------------
2020-04-07T08:20:30.000Z

如果日期时间字符串表达式和解析模式中未指定时区，则生成的datetime将具有用户通过time_zone/timezone REST/驱动程序参数指定的时区，无需进行任何转换。

{
    "query" : "SELECT DATETIME_PARSE('10:20:30 07/04/2020', 'HH:mm:ss dd/MM/yyyy') AS \"datetime\"",
    "time_zone" : "Europe/Athens"
}

      datetime
-----------------------------
2020-04-07T10:20:30.000+03:00

TIME_PARSE

编辑

概要

TIME_PARSE(
    string_exp, 
    string_exp)

输入:

时间表达式作为字符串。如果为null或空字符串，则函数返回null。

解析模式。如果为 null 或空字符串，则函数返回 null。

输出：时间

描述：使用第二个参数中指定的格式解析第一个参数，返回时间。使用的解析格式模式来自java.time.format.DateTimeFormatter。

如果解析模式仅包含日期单位（例如dd/MM/yyyy），则会返回错误，因为函数需要返回time类型的值，该值将仅包含时间。

SELECT TIME_PARSE('10:20:30.123', 'HH:mm:ss.SSS') AS "time";

     time
---------------
10:20:30.123Z
SELECT TIME_PARSE('10:20:30-01:00', 'HH:mm:ssXXX') AS "time";

     time
---------------
11:20:30.000Z

如果时间字符串表达式和解析模式中未指定时区，则生成的time将具有用户通过time_zone/timezone REST/驱动程序参数在Unix纪元日期（1970-01-01）指定的时区的偏移量，无需进行任何转换。

{
    "query" : "SELECT DATETIME_PARSE('10:20:30', 'HH:mm:ss') AS \"time\"",
    "time_zone" : "Europe/Athens"
}

      time
------------------------------------
10:20:30.000+02:00

DATE_PART/DATEPART

编辑

概要

DATE_PART(
    string_exp, 
    datetime_exp)

输入:

表示要从日期/日期时间中提取的单位的字符串表达式。如果为null，则函数返回null。

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：从日期/日期时间中提取指定的单位。它类似于EXTRACT，但单位的名称和别名不同，并提供更多选项（例如：TZOFFSET）。

要提取的日期时间单位

单位

缩写

年

years, yy, yyyy

季度

quarters, qq, q

月

months, mm, m

一年中的第几天

dy, y

日

days, dd, d

周

weeks, wk, ww

工作日

weekdays, dw

小时

hours, hh

分钟

minutes, mi, n

秒

seconds, ss, s

毫秒

milliseconds, ms

微秒

microseconds, mcs

纳秒

nanoseconds, ns

时区偏移量

时区

 
SELECT DATE_PART('year', '2019-09-22T11:22:33.123Z'::datetime) AS "years";

   years
----------
2019
SELECT DATE_PART('mi', '2019-09-04T11:22:33.123Z'::datetime) AS mins;

   mins
-----------
22
SELECT DATE_PART('quarters', CAST('2019-09-24' AS DATE)) AS quarter;

   quarter
-------------
3
SELECT DATE_PART('month', CAST('2019-09-24' AS DATE)) AS month;

   month
-------------
9

对于week和weekday，使用非ISO计算提取单位，这意味着给定的一周被认为是从星期日开始，而不是星期一。

SELECT DATE_PART('week', '2019-09-22T11:22:33.123Z'::datetime) AS week;

   week
----------
39

tzoffset返回表示时区偏移量的总分钟数（带符号）。

SELECT DATE_PART('tzoffset', '2019-09-04T11:22:33.123+05:15'::datetime) AS tz_mins;

   tz_mins
--------------
315
SELECT DATE_PART('tzoffset', '2019-09-04T11:22:33.123-03:49'::datetime) AS tz_mins;

   tz_mins
--------------
-229

DATE_TRUNC/DATETRUNC

编辑

概要

DATE_TRUNC(
    string_exp, 
    datetime_exp/interval_exp)

输入:

表示应将日期/日期时间/间隔截断到的单位的字符串表达式。如果为null，则函数返回null。

日期/日期时间/间隔表达式。如果为null，则函数返回null。

输出：日期时间/间隔

描述：通过将小于指定单位的所有字段设置为零（或对于日、星期几和月设置为一）来将日期/日期时间/间隔截断到指定的单位。如果第一个参数为week，而第二个参数为interval类型，则会抛出错误，因为interval数据类型不支持week时间单位。

日期时间截断单位

单位

缩写

千年

千年

世纪

世纪

十年

十年

年

years, yy, yyyy

季度

quarters, qq, q

月

months, mm, m

周

weeks, wk, ww

日

days, dd, d

小时

hours, hh

分钟

minutes, mi, n

秒

seconds, ss, s

毫秒

milliseconds, ms

微秒

microseconds, mcs

纳秒

nanoseconds, ns

 
SELECT DATE_TRUNC('millennium', '2019-09-04T11:22:33.123Z'::datetime) AS millennium;

      millennium
------------------------
2000-01-01T00:00:00.000Z
SELECT DATETRUNC('week', '2019-08-24T11:22:33.123Z'::datetime) AS week;

      week
------------------------
2019-08-19T00:00:00.000Z
SELECT DATE_TRUNC('mi', '2019-09-04T11:22:33.123Z'::datetime) AS mins;

      mins
------------------------
2019-09-04T11:22:00.000Z
SELECT DATE_TRUNC('decade', CAST('2019-09-04' AS DATE)) AS decades;

      decades
------------------------
2010-01-01T00:00:00.000Z
SELECT DATETRUNC('quarters', CAST('2019-09-04' AS DATE)) AS quarter;

      quarter
------------------------
2019-07-01T00:00:00.000Z
SELECT DATE_TRUNC('centuries', INTERVAL '199-5' YEAR TO MONTH) AS centuries;

      centuries
------------------
 +100-0
SELECT DATE_TRUNC('hours', INTERVAL '17 22:13:12' DAY TO SECONDS) AS hour;

      hour
------------------
+17 22:00:00
SELECT DATE_TRUNC('days', INTERVAL '19 15:24:19' DAY TO SECONDS) AS day;

      day
------------------
+19 00:00:00

FORMAT

编辑

概要

FORMAT(
    date_exp/datetime_exp/time_exp, 
    string_exp)

输入:

日期/日期时间/时间表达式。如果为 null，则函数返回 null。

格式模式。如果为 null 或空字符串，则函数返回 null。

输出：字符串

描述：使用第二个参数中指定的格式将日期/日期时间/时间作为字符串返回。使用的格式模式来自Microsoft SQL Server 格式规范。

如果第一个参数的类型为time，则第二个参数中指定的模式不能包含与日期相关的单位（例如dd、MM、yyyy等）。如果包含此类单位，则会返回错误。
格式说明符F的工作方式类似于格式说明符f。它将返回秒的小数部分，数字位数与作为输入提供的Fs的位数相同（最多9位）。结果将在末尾附加0以匹配提供的F的数量。例如：对于时间部分10:20:30.1234和模式HH:mm:ss.FFFFFF，函数的输出字符串将为：10:20:30.123400。
格式说明符y将返回年号，而不是一位/两位低位数字。例如：对于年份2009，y将返回2009而不是9。对于年份43，y格式说明符将返回43。 - 特殊字符如"、\和%将按原样返回，没有任何更改。例如：使用%M格式化日期17-sep-2020将返回%9

SELECT FORMAT(CAST('2020-04-05' AS DATE), 'dd/MM/yyyy') AS "date";

      date
------------------
05/04/2020
SELECT FORMAT(CAST('2020-04-05T11:22:33.987654' AS DATETIME), 'dd/MM/yyyy HH:mm:ss.ff') AS "datetime";

      datetime
------------------
05/04/2020 11:22:33.98
SELECT FORMAT(CAST('11:22:33.987' AS TIME), 'HH mm ss.f') AS "time";

      time
------------------
11 22 33.9

TO_CHAR

编辑

概要

TO_CHAR(
    date_exp/datetime_exp/time_exp, 
    string_exp)

输入:

日期/日期时间/时间表达式。如果为 null，则函数返回 null。

格式模式。如果为 null 或空字符串，则函数返回 null。

输出：字符串

描述：使用第二个参数中指定的格式将日期/日期时间/时间作为字符串返回。格式模式符合PostgreSQL 日期/时间格式的模板模式。

如果第一个参数的类型为time，则第二个参数中指定的模式不能包含与日期相关的单位（例如dd、MM、YYYY等）。如果包含此类单位，则会返回错误。
在某些情况下，模式TZ和tz（时区缩写）的结果与PostgreSQL中的TO_CHAR返回的结果不同。原因是JDK指定的时区缩写与PostgreSQL指定的时区缩写不同。此函数可能会显示实际的时区缩写，而不是PostgreSQL实现返回的通用LMT或空字符串或偏移量。两种实现之间的夏令时/日光节约时间标记也可能不同（例如，对于夏威夷，将显示HT而不是HST）。
不支持FX、TM、SP模式修饰符，它们将在输出中显示为FX、TM、SP字面量。

SELECT TO_CHAR(CAST('2020-04-05' AS DATE), 'DD/MM/YYYY') AS "date";

      date
------------------
05/04/2020
SELECT TO_CHAR(CAST('2020-04-05T11:22:33.987654' AS DATETIME), 'DD/MM/YYYY HH24:MI:SS.FF2') AS "datetime";

      datetime
------------------
05/04/2020 11:22:33.98
SELECT TO_CHAR(CAST('23:22:33.987' AS TIME), 'HH12 MI SS.FF1') AS "time";

      time
------------------
11 22 33.9

DAY_OF_MONTH/DOM/DAY

编辑

概要

DAY_OF_MONTH(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：从日期/日期时间中提取月份中的天数。

SELECT DAY_OF_MONTH(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
19

DAY_OF_WEEK/DAYOFWEEK/DOW

编辑

概要

DAY_OF_WEEK(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：从日期/日期时间中提取星期几。星期日为1，星期一为2，依此类推。

SELECT DAY_OF_WEEK(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
2

DAY_OF_YEAR/DOY

编辑

概要

DAY_OF_YEAR(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：从日期/日期时间中提取一年中的天数。

SELECT DAY_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50

DAY_NAME/DAYNAME

编辑

概要

DAY_NAME(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：字符串

描述：以文本格式（Monday、Tuesday…）从日期/日期时间中提取星期几。

SELECT DAY_NAME(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
Monday

HOUR_OF_DAY/HOUR

编辑

概要

HOUR_OF_DAY(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：从日期/日期时间中提取一天中的小时数。

SELECT HOUR_OF_DAY(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS hour;

     hour
---------------
10

ISO_DAY_OF_WEEK/ISODAYOFWEEK/ISODOW/IDOW

编辑

概要

ISO_DAY_OF_WEEK(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：根据ISO 8601标准从日期/日期时间中提取星期几。星期一为1，星期二为2，依此类推。

SELECT ISO_DAY_OF_WEEK(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
1

ISO_WEEK_OF_YEAR/ISOWEEKOFYEAR/ISOWEEK/IWOY/IW

编辑

概要

ISO_WEEK_OF_YEAR(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：根据ISO 8601标准从日期/日期时间中提取一年中的周数。一年的第一周是其大部分（4天或更多）日期都在一月的那一周。

SELECT ISO_WEEK_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS week;

     week
---------------
8

MINUTE_OF_DAY

编辑

概要

MINUTE_OF_DAY(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：从日期/日期时间中提取一天中的分钟数。

SELECT MINUTE_OF_DAY(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS minute;

    minute
---------------
623

MINUTE_OF_HOUR/MINUTE

编辑

概要

MINUTE_OF_HOUR(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：从日期/日期时间中提取一小时中的分钟数。

SELECT MINUTE_OF_HOUR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS minute;

    minute
---------------
23

MONTH_OF_YEAR/MONTH

编辑

概要

MONTH(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：从日期/日期时间中提取一年中的月份。

SELECT MONTH_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS month;

     month
---------------
2

MONTH_NAME/MONTHNAME

编辑

概要

MONTH_NAME(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：字符串

描述：以文本格式（January、February…）从日期/日期时间中提取月份。

SELECT MONTH_NAME(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS month;

     month
---------------
February

NOW

编辑

概要

NOW()

输入：无

输出：日期时间

描述：此函数的功能与CURRENT_TIMESTAMP()函数相同：返回当前查询到达服务器时的日期时间。此方法始终在其在同一查询中的每次出现时返回相同的值。

SELECT NOW() AS result;

         result
------------------------
2018-12-12T14:48:52.448Z

通常，此函数（及其孪生函数CURRENT_TIMESTAMP()）用于相对日期/时间筛选

SELECT first_name FROM emp WHERE hire_date > NOW() - INTERVAL 100 YEARS ORDER BY first_name ASC LIMIT 5;

  first_name
---------------
Alejandro
Amabile
Anneke
Anoosh
Arumugam

SECOND_OF_MINUTE/SECOND

编辑

概要

SECOND_OF_MINUTE(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：从日期/日期时间中提取一分钟中的秒数。

SELECT SECOND_OF_MINUTE(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS second;

    second
---------------
27

QUARTER

编辑

概要

QUARTER(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：提取日期/日期时间所属的年份季度。

SELECT QUARTER(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS quarter;

    quarter
---------------
1

TODAY

编辑

概要

TODAY()

输入：无

输出：日期

描述：此函数的功能与CURRENT_DATE()函数相同：返回当前查询到达服务器时的日期。此方法始终在其在同一查询中的每次出现时返回相同的值。

SELECT TODAY() AS result;

         result
------------------------
2018-12-12

通常，此函数（及其孪生函数CURRENT_TIMESTAMP()）用于相对日期过滤。

SELECT first_name FROM emp WHERE hire_date > TODAY() - INTERVAL 35 YEARS ORDER BY first_name ASC LIMIT 5;

 first_name
------------
Alejandro
Amabile
Anoosh
Basil
Bojan

WEEK_OF_YEAR/WEEK

编辑

概要

WEEK_OF_YEAR(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：从日期/时间中提取一年中的周数。

SELECT WEEK(CAST('1988-01-05T09:22:10Z' AS TIMESTAMP)) AS week, ISOWEEK(CAST('1988-01-05T09:22:10Z' AS TIMESTAMP)) AS isoweek;

      week     |   isoweek
---------------+---------------
2              |1

YEAR

编辑

概要

YEAR(datetime_exp)

输入:

日期/日期时间表达式。如果为 null，则函数返回 null。

输出：整数

描述：从日期/时间中提取年份。

SELECT YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS year;

     year
---------------
2018

EXTRACT

编辑

概要

EXTRACT(
    datetime_function  
    FROM datetime_exp)

输入:

日期/时间函数名称

日期/时间表达式

输出：整数

描述：通过指定日期时间函数的名称来从日期/时间中提取字段。以下是

SELECT EXTRACT(DAY_OF_YEAR FROM CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50

等同于

SELECT DAY_OF_YEAR(CAST('2018-02-19T10:23:27Z' AS TIMESTAMP)) AS day;

      day
---------------
50
« 分组函数 全文搜索函数 »