Presto 0.100 Documentation

5.8. 日期时间函数和运算符

5.8. 日期时间函数和运算符

日期时间运算符

运算符 示例 结果
+ 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 'America/Los_Angeles';
2012-10-30 18:00:00.000 America/Los_Angeles

日期时间函数

current_date -> date

Returns the current date as of the start of the query.

current_time -> time with time zone

Returns the current time as of the start of the query.

current_timestamp -> timestamp with time zone

Returns the current timestamp as of the start of the query.

current_timezone() → varchar

Returns the current time zone in the format defined by IANA (e.g., America/Los_Angeles) or as fixed offset from UTC (e.g., +08:35)

from_unixtime(unixtime) → timestamp

Returns the UNIX timestamp unixtime as a timestamp.

from_unixtime(unixtime, hours, minutes) → timestamp with time zone

Returns the UNIX timestamp unixtime as a timestamp with time zone using hours and minutes for the time zone offset.

localtime -> time

Returns the current time as of the start of the query.

localtimestamp -> timestamp

Returns the current timestamp as of the start of the query.

now() → timestamp with time zone

This is an alias for current_timestamp.

to_unixtime(timestamp) → double

Returns timestamp as a UNIX timestamp.

Note

下列SQL标准的函数不使用圆括号:

  • current_date
  • current_time
  • current_timestamp
  • localtime
  • localtimestamp

截取函数

函数 date_trunc 支持如下单位:

单位 Example Truncated Value
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之后的值

间隔函数

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

Unit Description
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的基础上加上valueunit。如果想要执行相减的操作,可以通过将value赋值为负数来完成。

date_diff(unit, timestamp1, timestamp2) → bigint

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

MySQL日期函数

在这一章节使用与MySQLdate_parsestr_to_date方法兼容的格式化字符串。下面的表格是基于MySQL手册列出的,描述了各种格式化描述符:

Specifier Description
%a Abbreviated weekday name (Sun .. Sat)
%b Abbreviated month name (Jan .. Dec)
%c Month, numeric (0 .. 12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, ...)
%d Day of the month, numeric (00 .. 31)
%e Day of the month, numeric (0 .. 31)
%f Microseconds (000000 .. 999999)
%H Hour (00 .. 23)
%h Hour (01 .. 12)
%I Hour (01 .. 12)
%i Minutes, numeric (00 .. 59)
%j Day of year (001 .. 366)
%k Hour (0 .. 23)
%l Hour (1 .. 12)
%M Month name (January .. December)
%m Month, numeric (00 .. 12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00 .. 59)
%s Seconds (00 .. 59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00 .. 53), where Sunday is the first day of the week
%u Week (00 .. 53), where Monday is the first day of the week
%V Week (01 .. 53), where Sunday is the first day of the week; used with %X
%v Week (01 .. 53), where Monday is the first day of the week; used with %x
%W Weekday name (Sunday .. Saturday)
%w Day of the week (0 .. 6), where Sunday is the first day of the week
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal % character
%x x, for any x not listed above

Warning

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

date_format(timestamp, format) → varchar

使用format指定的格式,将timestamp格式化成字符串。

date_parse(string, format) → timestamp

按照format指定的格式,将字符串string解析成timestamp。

Java日期函数

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

format_datetime(timestamp, format) → varchar

Formats timestamp as a string using format.

parse_datetime(string, format) → timestamp with time zone

Parses string into a timestamp with time zone using format.

抽取函数

可以使用抽取函数来抽取如下域:

Field Description
YEAR year()
QUARTER quarter()
MONTH month()
WEEK week()
DAY day()
DAY_OF_MONTH day()
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()

抽取函数支持的数据类型取决于需要抽取的域。大多数域都支持日期和时间类型。

extract(field FROM x) → bigint

x中返回域field

Note

SQL标准的函数一般都会使用特定的语法来指定参数。

便利的抽取函数

day(x) → bigint

Returns the day of the month from x.

day_of_month(x) → bigint

This is an alias for day().

day_of_week(x) → bigint

Returns the ISO day of the week from x. The value ranges from 1 (Monday) to 7 (Sunday).

day_of_year(x) → bigint

Returns the day of the year from x. The value ranges from 1 to 366.

dow(x) → bigint

This is an alias for day_of_week().

doy(x) → bigint

This is an alias for day_of_year().

hour(x) → bigint

Returns the hour of the day from x. The value ranges from 0 to 23.

minute(x) → bigint

Returns the minute of the hour from x.

month(x) → bigint

Returns the month of the year from x.

quarter(x) → bigint

Returns the quarter of the year from x. The value ranges from 1 to 4.

second(x) → bigint

Returns the second of the hour from x.

timezone_hour(timestamp) → bigint

Returns the hour of the time zone offset from timestamp.

timezone_minute(timestamp) → bigint

Returns the minute of the time zone offset from timestamp.

week(x) → bigint

Returns the ISO week of the year from x. The value ranges from 1 to 53.

week_of_year(x) → bigint

This is an alias for week().

year(x) → bigint

Returns the year from x.

year_of_week(x) → bigint

Returns the year of the ISO week from x.

yow(x) → bigint

This is an alias for year_of_week().