文档首页/ MapReduce服务 MRS/ 组件操作指南（LTS版）/ 使用HetuEngine/ HetuEngine常见SQL语法说明/ HetuEngine SQL函数和操作符说明/ 日期、时间函数及运算符

更新时间：2024-10-28 GMT+08:00

日期、时间函数及运算符

日期时间运算符

运算符	示例	结果
+	date '2012-08-08' + interval '2' day	2012-08-10
+	time '01:00' + interval '3' hour	04:00:00.000
+	timestamp '2012-08-08 01:00' + interval '29' hour	2012-08-09 06:00:00.000
+	timestamp '2012-10-31 01:00' + interval '1' month	2012-11-30 01:00:00.000
+	interval '2' day + interval '3' hour	2 03:00:00.000
+	interval '3' year + interval '5' month	3-5
-	date '2012-08-08' - interval '2' day	2012-08-06
-	time '01:00' - interval '3' hour	22:00:00.000
-	timestamp '2012-08-08 01:00' - interval '29' hour	2012-08-06 20:00:00.000
-	timestamp '2012-10-31 01:00' - interval '1' month	2012-09-30 01:00:00.000
-	interval '2' day - interval '3' hour	1 21:00:00.000
-	interval '3' year - interval '5' month	2-7

时区转换

运算符：AT TIME ZONE，用于设置一个时间戳的时区。

SELECT timestamp '2012-10-31 01:00 UTC';-- 2012-10-31 01:00:00.000 UTC
SELECT timestamp '2012-10-31 01:00 UTC' AT TIME ZONE 'Asia/Singapore'; -- 2012-10-30 09:00:00.000 Asia/Singapore

日期时间函数

current_date -> date
返回当前日期(utc时区)
```
select current_date; -- 2020-07-25 
```

current_time -> time with time zone
返回当前时间(utc时区)
```
select current_time;-- 16:58:48.601+08:00
```
current_timestamp -> timestamp with time zone
返回当前时间戳(当前时区)
```
select current_timestamp; -- 2020-07-25 11:50:27.350 Asia/Singapore
```
current_timezone() → varchar
返回当前时区
```
select current_timezone();-- Asia/Singapore 
```
date(x) → date
将日期字面量转换成日期类型的变量
```
select date('2020-07-25');-- 2020-07-25
```

from_iso8601_timestamp(string) → timestamp with time zone

将ISO 8601格式的时戳字面量转换成带时区的时戳变量

SELECT from_iso8601_timestamp('2020-05-11');-- 2020-05-11 00:00:00.000 Asia/Singapore
SELECT from_iso8601_timestamp('2020-05-11T11:15:05'); -- 2020-05-11 11:15:05.000 Asia/Singapore 
SELECT from_iso8601_timestamp('2020-05-11T11:15:05.055+01:00');-- 2020-05-11 11:15:05.055 +01:00

from_iso8601_date(string) → date

将ISO 8601格式的日期字面量转换成日期类型的变量

SELECT from_iso8601_date('2020-05-11');-- 2020-05-11
SELECT from_iso8601_date('2020-W10');-- 2020-03-02
SELECT from_iso8601_date('2020-123');-- 2020-05-02

from_unixtime(unixtime) → timestamp with time zone

将UNIX时戳转换为时间戳变量（当前时区）

Select FROM_UNIXTIME(1.595658735E9); -- 2020-07-25 14:32:15.000 Asia/Singapore
Select FROM_UNIXTIME(875996580); --1997-10-05 04:23:00.000 Asia/Singapore

from_unixtime(unixtime, string) → timestamp with time zone
将UNIX时戳转换成时戳变量，可以带时区选项
```
select from_unixtime(1.595658735E9, 'Asia/Singapore');-- 2020-07-25 14:32:15.000 Asia/Singapore 
```
from_unixtime(unixtime, hours, minutes) → timestamp with time zone
将UNIX时戳转换成带时区的时戳变量，hours和minutes表示时区偏移量
```
select from_unixtime(1.595658735E9, 8, 30);-- 2020-07-25 14:32:15.000 +08:30
```
localtime -> time
获取当前时间
```
select localtime;-- 14:16:13.096 
```

localtimestamp -> timestamp

获取当前时间戳

select localtimestamp;-- 2020-07-25 14:17:00.567

months_between(date1, date2) -> double
返回date1和date2之间的月数，如果date1比date2晚，结果就是正数，那么结果就是负数；如果两个日期的日数相同，那么结果就是整数，否则按照每月31天以及时分秒的差异来计算小数部分。date1和date2的类型可以是date，timestamp，也可以是“yyyy-MM-dd”或“yyyy-MM-dd HH:mm:ss”格式的字符串
```
select months_between('2020-02-28 10:30:00', '2021-10-30');-- -20.05040323
select months_between('2021-01-30', '2020-10-30'); -- 3.0
```
now() → timestamp with time zone
获取当前时间，current_timestamp的别名
```
select now();-- 2020-07-25 14:39:39.842 Asia/Singapore
```
unix_timestamp()
获取当前unix时间戳
```
select unix_timestamp(); -- 1600930503
```

to_iso8601(x) → varchar

将x转换成ISO8601格式的字符串。这里x可以是DATE、TIMESTAMP [with time zone]这几个类型

select to_iso8601(date '2020-07-25'); -- 2020-07-25
select to_iso8601(timestamp '2020-07-25 15:22:15.214'); -- 2020-07-25T15:22:15.214

to_milliseconds(interval) → bigint
获取当前距当天零时已经过去的毫秒数
```
select to_milliseconds(interval '8' day to second);-- 691200000
```

to_unixtime(timestamp) → double

将时间戳转换成UNIX时间

select to_unixtime(cast('2020-07-25 14:32:15.147' as timestamp));-- 1.595658735147E9

trunc(string date, string format) →string
按照format格式去截取日期值，支持的格式有：MONTH/MON/MM，YEAR/YYYY/YY, QUARTER/Q
```
select trunc(date '2020-07-08','yy');-- 2020-01-01
select trunc(date '2020-07-08','MM');-- 2020-07-01
```
使用下列 SQL 标准函数时，兼容使用圆括号的方式：
- current_date
- current_time
- current_timestamp
- localtime
- Localtimestamp
如：select current_date();

截取函数

类似于保留几位小数的操作，函数 date_trunc 支持如下单位：

单位	截取后的值
second	2001-08-22 03:04:05.000
minute	2001-08-22 03:04:00.000
hour	2001-08-22 03:00:00.000
day	2001-08-22 00:00:00.000
week	2001-08-20 00:00:00.000
month	2001-08-01 00:00:00.000
quarter	2001-07-01 00:00:00.000
year	2001-01-01 00:00:00.000

上面的例子使用时间戳2001-08-22 03:04:05.321作为输入。

date_trunc(unit, x) → [same as input]

返回x截取到单位unit之后的值。

select date_trunc('hour', timestamp '2001-08-22 03:04:05.321'); -- 2001-08-22 03:00:00.000

间隔函数

本章中的函数支持如下所列的间隔单位：

单位	描述
second	Seconds
minute	Minutes
hour	Hours
day	Days
week	Weeks
month	Months
quarter	Quarters of a year
year	Years

date_add(unit, value, timestamp) → [same as input]

在timestamp的基础上加上value个unit。如果想要执行相减的操作，可以通过将value赋值为负数来完成。

SELECT date_add('second', 86, TIMESTAMP '2020-03-01 00:00:00');-- 2020-03-01 00:01:26
SELECT date_add('hour', 9, TIMESTAMP '2020-03-01 00:00:00');-- 2020-03-01 09:00:00
SELECT date_add('day', -1, TIMESTAMP '2020-03-01 00:00:00 UTC');-- 2020-02-29 00:00:00 UTC

date_diff(unit, timestamp1, timestamp2) → bigint

返回timestamp2 - timestamp1之后的值，该值的表示单位是unit。

unit的值是字符串。例如：‘day’、‘week’、‘year’

SELECT date_diff('second', TIMESTAMP '2020-03-01 00:00:00', TIMESTAMP '2020-03-02 00:00:00');-- 86400
SELECT date_diff('hour', TIMESTAMP '2020-03-01 00:00:00 UTC', TIMESTAMP '2020-03-02 00:00:00 UTC');-- 24
SELECT date_diff('day', DATE '2020-03-01', DATE '2020-03-02');-- 1
SELECT date_diff('second', TIMESTAMP '2020-06-01 12:30:45.000', TIMESTAMP '2020-06-02 12:30:45.123');-- 86400
SELECT date_diff('millisecond', TIMESTAMP '2020-06-01 12:30:45.000', TIMESTAMP '2020-06-02 12:30:45.123');-- 86400123

adddate(date, bigint)→ [same as input]
描述：日期加法。输入的类型可以是date或timestamp，表示对日期做加减，当做减法时，bigint对应值为负。
```
select ADDDATE(timestamp '2020-07-04 15:22:15.124',-5);-- 2020-06-29 15:22:15.124 
select ADDDATE(date '2020-07-24',5); -- 2020-07-29
```

持续时间函数

持续时间可以使用以下单位：

单位	描述
ns	纳秒
us	微秒
ms	毫秒
s	秒
m	分钟
h	小时
d	天

parse_duration(string) → interval

SELECT parse_duration('42.8ms'); -- 0 00:00:00.043
SELECT parse_duration('3.81 d'); -- 3 19:26:24.000
SELECT parse_duration('5m'); -- 0 00:05:00.000

MySQL 日期函数

在这一章节使用与MySQL date_parse和str_to_date方法兼容的格式化字符串。

date_format(timestamp, format) → varchar

使用format格式化timestamp。

select date_format(timestamp '2020-07-22 15:00:15', '%Y/%m/%d');-- 2020/07/22

date_parse(string, format) → timestamp
按format格式解析日期字面量。
```
select date_parse('2020/07/20', '%Y/%m/%d');-- 2020-07-20 00:00:00.000 
```

下面的表格是基于MySQL手册列出的，描述了各种格式化描述符：

格式化描述符	描述
%a	对应的星期几（Sun .. Sat）
%b	对应的月份（Jan .. Dec）
%c	对应的月份（1 .. 12）
%D	对应该月的第几天（0th, 1st, 2nd, 3rd, ...）
%d	对应该月的第几天，数字（01 .. 31）（两位，前面会补0）
%e	对应该月的第几天，数字（1 .. 31）
%f	小数以下的秒（6 digits for printing: 000000 .. 999000; 1 - 9 digits for parsing: 0 .. 999999999）
%H	小时（00 .. 23）
%h	小时（01 .. 12）
%I	小时（01 .. 12）
%i	分钟，数字（00 .. 59）
%j	一年的第几天（001 .. 366）
%k	小时（0 .. 23）
%l	小时（1 .. 12）
%M	月份名称（January .. December）
%m	月份，数字（01 .. 12）
%p	AM or PM
%r	时间，12小时制（hh:mm:ss followed by AM or PM）
%S	秒（00 .. 59）
%s	秒（00 .. 59）
%T	时间，24小时制（hh:mm:ss）
%U	周（00 .. 53），星期天是一周的第一天
%u	周（00 .. 53），星期一是一周的第一天
%V	周（01 .. 53），星期天是一周的第一天，与%X配合使用
%v	星期（01 .. 53），第一条为星期一，与%X配合使用
%W	周几（Sunday .. Saturday）
%w	本周的第几天（0 .. 6），星期天是一周的第一天
%X	年份，数字，4位，第一天为星期日
%x	年份，数字，4位，第一天为星期一
%Y	年份，数字，4位
%y	年份，数字，2位，表示年份范围为[1970, 2069]
%%	表示字符'%'

示例：

select date_format(timestamp '2020-07-25 15:04:00.124','一年的第%j天，%m月的第%d天，%p %T %W');
                       _col0                       
---------------------------------------------------
 一年的第207天，07月的第25天，PM 15:04:00 Saturday 
(1 row)

这些格式化描述符现在还不支持：%D、%U、%u、%V、%w、%X。

date_format(timestamp, format) → varchar
使用format格式化timestamp

date_parse(string, format) → timestamp

解析时间戳字符串

select date_parse('2020/07/20', '%Y/%m/%d');-- 2020-07-20 00:00:00.000

Java 日期函数

在这一章节中使用的格式化字符串都是与Java的SimpleDateFormat样式兼容的。

format_datetime(timestamp, format) → varchar
使用format格式化timestamp

parse_datetime(string, format) → timestamp with time zone

使用指定的格式，将字符串格式化为timestamp with time zone

select parse_datetime('1960/01/22 03:04', 'yyyy/MM/dd HH:mm');
                 _col0                 
---------------------------------------
 1960-01-22 03:04:00.000 Asia/Shanghai 
(1 row)

常用提取函数

域	描述
YEAR	year()
QUARTER	quarter()
MONTH	month()
WEEK	week()
DAY	day()
DAY_OF_MONTH	day_of_month()
DAY_OF_WEEK	day_of_week()
DOW	day_of_week()
DAY_OF_YEAR	day_of_year()
DOY	day_of_year()
YEAR_OF_WEEK	year_of_week()
YOW	year_of_week()
HOUR	hour()
MINUTE	minute()
SECOND	second()
TIMEZONE_HOUR	timezone_hour()
TIMEZONE_MINUTE	timezone_minute()

例如：

select second(timestamp '2020-02-12 15:32:33.215');-- 33
select timezone_hour(timestamp '2020-02-12 15:32:33.215');-- 8

MONTHNAME(date)

描述：获取月份名称。

SELECT monthname(timestamp '2019-09-09 12:12:12.000');-- SEPTEMBER
SELECT monthname(date '2019-07-09');--JULY

extract(field FROM x) → bigint

描述：从x中返回域，对应的域字段，参照本篇的表格。

select extract(YOW FROM timestamp '2020-02-12 15:32:33.215');-- 2020
select extract(SECOND FROM timestamp '2020-02-12 15:32:33.215');-- 33
select extract(DOY FROM timestamp '2020-02-12 15:32:33.215');--43

函数	示例	描述
SECONDS_ADD(TIMESTAMP date, INT seconds)	SELECT seconds_add(timestamp '2019-09-09 12:12:12.000', 10);	给时间以秒为单位进行加法
SECONDS_SUB(TIMESTAMP date, INT seconds)	SELECT seconds_sub(timestamp '2019-09-09 12:12:12.000', 10);	给时间以秒为单位进行减法
MINUTES_ADD(TIMESTAMP date, INT minutes)	SELECT MINUTES_ADD(timestamp '2019-09-09 12:12:12.000', 10);	给时间以分钟为单位进行加法
MINUTES_SUB(TIMESTAMP date, INT minutes)	SELECT MINUTES_SUB(timestamp '2019-09-09 12:12:12.000', 10);	给时间以分钟为单位进行减法
HOURS_ADD(TIMESTAMP date, INT hours)	SELECT HOURS_ADD(timestamp '2019-09-09 12:12:12.000', 1);	给时间以小时为单位进行加法
HOURS_SUB(TIMESTAMP date, INT hours)	SELECT HOURS_SUB(timestamp '2019-09-09 12:12:12.000', 1);	给时间以小时为单位进行减法

last_day(timestamp) -> date

描述：根据指定的时间戳返回每个月的最后一天。

SELECT last_day(timestamp '2019-09-09 12:12:12.000');--  2019-09-30
SELECT last_day(date '2019-07-09');--2019-07-31

add_months(timestamp) -> [same as input]
描述：通过将指定的月份增加指定的日期来返回正确的日期。
```
SELECT add_months(timestamp'2019-09-09 00:00:00.000', 11);-- 2020-08-09 00:00:00.000
```

next_day() (timestamp, string) -> date

描述：根据指定日期返回指定周几的下一天日期。

SELECT next_day(timestamp'2019-09-09 00:00:00.000', 'monday');-- 2019-09-16 00:00:00.000
SELECT next_day(date'2019-09-09', 'monday');-- 2019-09-16

numtoday(integer) -> BIGINT
描述：将传递的整数值转换为day类型，例如BIGINT类型。
```
SELECT numtoday(2);-- 2
```

父主题： HetuEngine SQL函数和操作符说明

上一篇：Json函数和运算符

下一篇：聚合函数

意见反馈

文档内容是否对您有帮助？

有帮助没帮助

提供反馈

提交成功！非常感谢您的反馈，我们会继续努力做到更好！

系统繁忙，请稍后重试

在使用文档中是否遇到以下问题

内容与产品页面不一致

内容不易理解

缺失示例代码

步骤不可操作

搜不到想要的内容

缺少最佳实践

意见反馈（选填）

0/500

请至少选择一项反馈信息并填写问题反馈

字符长度不能超过500

直接提交取消