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

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

编辑

日期/时间和间隔函数与运算符编辑

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

间隔编辑

在处理日期/时间时，一个常见的需求是围绕 间隔 的概念，这是一个值得在 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 秒 123000000 纳秒

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() 接受精度作为可选参数，用于对秒小数位数（纳秒）进行舍入。默认精度为 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 的精度不会对函数的输出产生任何影响，因为返回的秒小数位数的最大数量为 6。

CURRENT_TIMESTAMP编辑

概要

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP([precision])

输入:

小数位数；可选

输出: 日期/时间

描述: 返回当前查询到达服务器时的日期/时间。作为函数，CURRENT_TIMESTAMP() 接受精度作为可选参数，用于对秒小数位数（纳秒）进行舍入。默认精度为 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 的精度不会对函数的输出产生任何影响，因为返回的秒小数位数的最大数量为 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) 的范围从 1 开始，与 MySQL 不同，MySQL 中的范围从 0 开始，因为 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 类型的值，该值将包含日期部分。

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_FORMATedit

概要

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_PARSEedit

概要

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_PARSEedit

概要

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/DATEPARTedit

概要

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

tzoffset

tz

 
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/DATETRUNCedit

概要

DATE_TRUNC(
    string_exp, 
    datetime_exp/interval_exp)

输入:

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

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

输出: 日期时间/间隔

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

日期时间截断单位

单位

缩写

millennium

millennia

century

centuries

decade

decades

年

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

FORMATedit

概要

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_CHARedit

概要

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/DAYedit

概要

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/DOWedit

概要

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/DOYedit

概要

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/DAYNAMEedit

概要

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/HOURedit

概要

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/IDOWedit

概要

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/IWedit

概要

ISO_WEEK_OF_YEAR(datetime_exp)

输入:

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

输出: 整数

描述: 从日期/时间中提取一年中的周数，遵循 ISO 8601 标准。一年的第一周是第一周，其大部分（4 天或更多）在 1 月份。

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
« 分组函数 全文搜索函数 »