本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。
日期和时间函数
注意
要查看哪些 AWS 数据源集成支持此SQL命令，请参阅支持的 OpenSearch SQL命令和函数。
| 函数 | 描述 |
|---|---|
| 添加月（开始日期，数字_月） | 返回之num_months后的日期start_date。 |
| 转换时区 ([sourceTz,],) targetTz sourceTs | 将不带时区的时间戳sourceTs从sourceTz时区转换为。targetTz |
| curdate () | 返回查询评估开始时的当前日期。在同一个查询中对 curdate 的所有调用都返回相同的值。 |
| 当前日期 () | 返回查询评估开始时的当前日期。在同一个查询中对 current_date 的所有调用都会返回相同的值。 |
| 当前日期 | 返回查询评估开始时的当前日期。 |
| 当前时间戳 () | 返回查询评估开始时的当前时间戳。在同一个查询中对 current_timestamp 的所有调用都会返回相同的值。 |
| 当前时间戳 | 返回查询评估开始时的当前时间戳。 |
| 当前时区 () | 返回当前会话的本地时区。 |
| 日期_add（开始日期、天数） | 返回之num_days后的日期start_date。 |
| date_diff (,) endDate startDate | 返回从startDate到的天数endDate。 |
| 日期格式（时间戳，fmt） | timestamp以日期格式指定的格式转换为字符串值fmt。 |
| date_from_unix_date（天） | 根据自 1970 年 1 月 1 日以来的天数创建日期。 |
| date_part（字段、来源） | 提取日期/时间戳或间隔源的一部分。 |
| date_sub（起始日期，天数） | 返回num_days之前的日期start_date。 |
| date_trunc (fmt, ts) | 返回ts截断为格式模型指定的单位的时间戳。fmt |
| dateadd（开始日期、天数） | 返回之num_days后的日期start_date。 |
| datediff (endDate,) startDate | 返回从startDate到的天数endDate。 |
| 日期部分（字段、来源） | 提取日期/时间戳或间隔源的一部分。 |
| 日（日期） | 返回日期/时间戳的月份中的某一天。 |
| 一个月中的某天（日期） | 返回日期/时间戳的月份中的某一天。 |
| 一周中的某一天（日期） | 返回日期/时间戳的一周中的某一天（1 = 星期日，2 = 星期一，...，7 = 星期六）。 |
| 一年中的某一天（日期） | 返回日期/时间戳中的某一年。 |
| 提取（字段FROM源） | 提取日期/时间戳或间隔源的一部分。 |
| from_unixtime (unix_time [, fmt]) | 以指定的unix_time形式返回fmt。 |
| from_utc_timestamp（时间戳、时区） | 给定一个像 '2017-07-14 02:40:00.0 '这样的时间戳，将其解释为时间点UTC，并将该时间渲染为给定时区的时间戳。例如，“GMT+1” 将产生 “2017-07-14 03:40:00.0”。 |
| 小时（时间戳） | 返回字符串/时间戳的小时部分。 |
| 最后一天（日期） | 返回该日期所属月份的最后一天。 |
| 本地时间戳 () | 返回查询评估开始时不带时区的当前时间戳。在同一个查询中对 localtimestamp 的所有调用都会返回相同的值。 |
| 本地时间戳 | 返回查询评估开始时会话时区的当前本地日期时间。 |
| make_date（年、月、日） | 根据年、月和日字段创建日期。 |
| make_dt_interval ([天 [, 小时 [, 分钟 [, 秒]]]]]) | 以天、小时、分钟和秒为单位制作 DayTimeIntervalType 持续时间。 |
| make_interval（[年 [，月 [，周 [，天 [，小时 [，分钟 [，秒]]]]]]]]]]]]]） | 按年、月、周、天、小时、分钟和秒进行间隔。 |
| make_timestamp（年、月、日、小时、分钟、秒 [，时区]） | 根据年、月、日、小时、分钟、秒和时区字段创建时间戳。 |
| make_timestamp_ltz（年、月、日、小时、分钟、秒 [，时区]） | 使用年、月、日、小时、分钟、秒和时区字段中的本地时区创建当前时间戳。 |
| make_timestamp_ntz（年、月、日、小时、分钟、秒） | 根据年、月、日、小时、分钟、秒字段创建本地日期时间。 |
| make_ym_interval ([年 [，月]]) | 将年月与年、月进行间隔。 |
| 分钟（时间戳） | 返回字符串/时间戳的分钟部分。 |
| 月（日期） | 返回日期/时间戳的月份部分。 |
| months_between（时间戳 1、timestamp2 [、]）roundOff | 如果晚timestamp1于timestamp2，则结果为正。如果timestamp1和timestamp2在一个月的同一天，或者两者都是一个月的最后一天，则一天中的时间将被忽略。否则，差额按每月 31 天计算，除非 roundOff =false，否则四舍五入为 8 位数。 |
| 下一天（开始日期、一周中的某一天） | 返回晚于start_date并按指示命名的第一个日期。NULL如果至少有一个输入参数是，则该函数返回NULL。 |
| 现在 () | 返回查询评估开始时的当前时间戳。 |
| 季度（日期） | 返回日期在一年中的季度，范围为 1 到 4。 |
| 秒（时间戳） | 返回字符串/时间戳的第二个组成部分。 |
| 会话窗口（时间列，间隙持续时间） | 生成会话窗口，给定时间戳，指定列和间隙持续时间。有关详细说明和示例，请参阅结构化直播指南文档中的 “时间窗类型”。 |
| timestamp_micros（微秒） | 根据自UTC纪元以来的微秒数创建时间戳。 |
| timestamp_millis（毫秒） | 根据自纪元以来的毫秒数创建时间戳。UTC |
| timestamp_seconds（秒） | 根据自UTC纪元以来的秒数（可以是小数）创建时间戳。 |
| to_date (date_str [, fmt]) | 将带有date_str表达式的表达fmt式解析为日期。如果输入无效，则返回 null。默认情况下，如果省略了，它将遵循投射规则直到fmt某个日期。 |
| to_timestamp (timestamp_str [, fmt]) | 将带有timestamp_str表达式的表达式解析为时间戳。fmt如果输入无效，则返回 null。默认情况下，如果省略了，它将遵循时间戳的fmt强制转换规则。 |
| to_timestamp_ltz (timestamp_str [, fmt]) | 将带有timestamp_str表达式的表达fmt式解析为具有本地时区的时间戳。如果输入无效，则返回 null。默认情况下，如果省略了，它将遵循时间戳的fmt强制转换规则。 |
| to_timestamp_ntz (timestamp_str [, fmt]) | 将带有timestamp_str表达式的表达式解析为不带时区的时间戳。fmt如果输入无效，则返回 null。默认情况下，如果省略了，它将遵循时间戳的fmt强制转换规则。 |
| to_unix_timestamp ([, fmt]) timeExp | 返回给定UNIX时间的时间戳。 |
| to_utc_timestamp（时间戳、时区） | 给定一个像 '2017-07-14 02:40:00.0 '这样的时间戳，将其解释为给定时区的时间，并将该时间渲染为时间戳。UTC例如，“GMT+1” 将产生 “2017-07-14 01:40:00.0”。 |
| trunc（日期，fmt） | 返回date时将一天中的时间部分截断为格式模型指定的单位。fmt |
| try_to_timestamp (timestamp_str [, fmt]) | 将带有timestamp_str表达式的表达式解析为时间戳。fmt |
| unix_date（日期） | 返回自 1970-01-01 以来的天数。 |
| unix_micros（时间戳） | 返回自 1970-01-01 00:00:00 以来的微秒数。UTC |
| unix_millis（时间戳） | 返回自 1970-01-01 00:00:00 以来的毫秒数。UTC截断更高的精度级别。 |
| unix_seconds（时间戳） | 返回自 1970-01-01 00:00:00 以来的秒数。UTC截断更高的精度级别。 |
| unix_timestamp ([timeExp[, fmt]]) | 返回当前或指定时间的时间UNIX戳。 |
| 工作日（日期） | 返回日期/时间戳的一周中的某一天（0 = 星期一，1 = 星期二，...，6 = 星期日）。 |
| 一年中的一周（日期） | 返回给定日期当年中的某周。一周被认为从星期一开始，第 1 周是第一个超过 3 天的周。 |
| 窗口（time_column、window_duration [、window_duration [、start_time]]） | 给定指定列的时间戳，将行分成一个或多个时间窗口。窗口开头包含在内，但窗口结尾是排他性的，例如 12:05 将在窗口 [12:05,12:10) 中，但不在 [12:00,12:05) 中。Windows 可以支持微秒精度。不支持以月为单位的 Windows。有关详细说明和示例，请参阅结构化流媒体指南文档中的 “事件时间的窗口操作”。 |
| window_time（window_column） | 从时间/会话窗口列中提取时间值，该列可用于窗口的事件时间值。提取的时间是 (window.end-1)，这反映了这样一个事实，即聚合窗口具有排他性上限-[开始，结束) 有关详细说明和示例，请参阅结构化流媒体指南文档中的 “事件时间的窗口操作”。 |
| 年（日期） | 返回日期/时间戳的年份部分。 |
示例
-- add_months SELECT add_months('2016-08-31', 1); +-------------------------+ |add_months(2016-08-31, 1)| +-------------------------+ | 2016-09-30| +-------------------------+ -- convert_timezone SELECT convert_timezone('Europe/Brussels', 'America/Los_Angeles', timestamp_ntz'2021-12-06 00:00:00'); +-------------------------------------------------------------------------------------------+ |convert_timezone(Europe/Brussels, America/Los_Angeles, TIMESTAMP_NTZ '2021-12-06 00:00:00')| +-------------------------------------------------------------------------------------------+ | 2021-12-05 15:00:00| +-------------------------------------------------------------------------------------------+ SELECT convert_timezone('Europe/Brussels', timestamp_ntz'2021-12-05 15:00:00'); +------------------------------------------------------------------------------------------+ |convert_timezone(current_timezone(), Europe/Brussels, TIMESTAMP_NTZ '2021-12-05 15:00:00')| +------------------------------------------------------------------------------------------+ | 2021-12-05 07:00:00| +------------------------------------------------------------------------------------------+ -- curdate SELECT curdate(); +--------------+ |current_date()| +--------------+ | 2024-02-24| +--------------+ -- current_date SELECT current_date(); +--------------+ |current_date()| +--------------+ | 2024-02-24| +--------------+ SELECT current_date; +--------------+ |current_date()| +--------------+ | 2024-02-24| +--------------+ -- current_timestamp SELECT current_timestamp(); +--------------------+ | current_timestamp()| +--------------------+ |2024-02-24 16:36:...| +--------------------+ SELECT current_timestamp; +--------------------+ | current_timestamp()| +--------------------+ |2024-02-24 16:36:...| +--------------------+ -- current_timezone SELECT current_timezone(); +------------------+ |current_timezone()| +------------------+ | Asia/Seoul| +------------------+ -- date_add SELECT date_add('2016-07-30', 1); +-----------------------+ |date_add(2016-07-30, 1)| +-----------------------+ | 2016-07-31| +-----------------------+ -- date_diff SELECT date_diff('2009-07-31', '2009-07-30'); +---------------------------------+ |date_diff(2009-07-31, 2009-07-30)| +---------------------------------+ | 1| +---------------------------------+ SELECT date_diff('2009-07-30', '2009-07-31'); +---------------------------------+ |date_diff(2009-07-30, 2009-07-31)| +---------------------------------+ | -1| +---------------------------------+ -- date_format SELECT date_format('2016-04-08', 'y'); +--------------------------+ |date_format(2016-04-08, y)| +--------------------------+ | 2016| +--------------------------+ -- date_from_unix_date SELECT date_from_unix_date(1); +----------------------+ |date_from_unix_date(1)| +----------------------+ | 1970-01-02| +----------------------+ -- date_part SELECT date_part('YEAR', TIMESTAMP '2019-08-12 01:00:00.123456'); +-------------------------------------------------------+ |date_part(YEAR, TIMESTAMP '2019-08-12 01:00:00.123456')| +-------------------------------------------------------+ | 2019| +-------------------------------------------------------+ SELECT date_part('week', timestamp'2019-08-12 01:00:00.123456'); +-------------------------------------------------------+ |date_part(week, TIMESTAMP '2019-08-12 01:00:00.123456')| +-------------------------------------------------------+ | 33| +-------------------------------------------------------+ SELECT date_part('doy', DATE'2019-08-12'); +---------------------------------+ |date_part(doy, DATE '2019-08-12')| +---------------------------------+ | 224| +---------------------------------+ SELECT date_part('SECONDS', timestamp'2019-10-01 00:00:01.000001'); +----------------------------------------------------------+ |date_part(SECONDS, TIMESTAMP '2019-10-01 00:00:01.000001')| +----------------------------------------------------------+ | 1.000001| +----------------------------------------------------------+ SELECT date_part('days', interval 5 days 3 hours 7 minutes); +-------------------------------------------------+ |date_part(days, INTERVAL '5 03:07' DAY TO MINUTE)| +-------------------------------------------------+ | 5| +-------------------------------------------------+ SELECT date_part('seconds', interval 5 hours 30 seconds 1 milliseconds 1 microseconds); +-------------------------------------------------------------+ |date_part(seconds, INTERVAL '05:00:30.001001' HOUR TO SECOND)| +-------------------------------------------------------------+ | 30.001001| +-------------------------------------------------------------+ SELECT date_part('MONTH', INTERVAL '2021-11' YEAR TO MONTH); +--------------------------------------------------+ |date_part(MONTH, INTERVAL '2021-11' YEAR TO MONTH)| +--------------------------------------------------+ | 11| +--------------------------------------------------+ SELECT date_part('MINUTE', INTERVAL '123 23:55:59.002001' DAY TO SECOND); +---------------------------------------------------------------+ |date_part(MINUTE, INTERVAL '123 23:55:59.002001' DAY TO SECOND)| +---------------------------------------------------------------+ | 55| +---------------------------------------------------------------+ -- date_sub SELECT date_sub('2016-07-30', 1); +-----------------------+ |date_sub(2016-07-30, 1)| +-----------------------+ | 2016-07-29| +-----------------------+ -- date_trunc SELECT date_trunc('YEAR', '2015-03-05T09:32:05.359'); +-----------------------------------------+ |date_trunc(YEAR, 2015-03-05T09:32:05.359)| +-----------------------------------------+ | 2015-01-01 00:00:00| +-----------------------------------------+ SELECT date_trunc('MM', '2015-03-05T09:32:05.359'); +---------------------------------------+ |date_trunc(MM, 2015-03-05T09:32:05.359)| +---------------------------------------+ | 2015-03-01 00:00:00| +---------------------------------------+ SELECT date_trunc('DD', '2015-03-05T09:32:05.359'); +---------------------------------------+ |date_trunc(DD, 2015-03-05T09:32:05.359)| +---------------------------------------+ | 2015-03-05 00:00:00| +---------------------------------------+ SELECT date_trunc('HOUR', '2015-03-05T09:32:05.359'); +-----------------------------------------+ |date_trunc(HOUR, 2015-03-05T09:32:05.359)| +-----------------------------------------+ | 2015-03-05 09:00:00| +-----------------------------------------+ SELECT date_trunc('MILLISECOND', '2015-03-05T09:32:05.123456'); +---------------------------------------------------+ |date_trunc(MILLISECOND, 2015-03-05T09:32:05.123456)| +---------------------------------------------------+ | 2015-03-05 09:32:...| +---------------------------------------------------+ -- dateadd SELECT dateadd('2016-07-30', 1); +-----------------------+ |date_add(2016-07-30, 1)| +-----------------------+ | 2016-07-31| +-----------------------+ -- datediff SELECT datediff('2009-07-31', '2009-07-30'); +--------------------------------+ |datediff(2009-07-31, 2009-07-30)| +--------------------------------+ | 1| +--------------------------------+ SELECT datediff('2009-07-30', '2009-07-31'); +--------------------------------+ |datediff(2009-07-30, 2009-07-31)| +--------------------------------+ | -1| +--------------------------------+ -- datepart SELECT datepart('YEAR', TIMESTAMP '2019-08-12 01:00:00.123456'); +----------------------------------------------------------+ |datepart(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456')| +----------------------------------------------------------+ | 2019| +----------------------------------------------------------+ SELECT datepart('week', timestamp'2019-08-12 01:00:00.123456'); +----------------------------------------------------------+ |datepart(week FROM TIMESTAMP '2019-08-12 01:00:00.123456')| +----------------------------------------------------------+ | 33| +----------------------------------------------------------+ SELECT datepart('doy', DATE'2019-08-12'); +------------------------------------+ |datepart(doy FROM DATE '2019-08-12')| +------------------------------------+ | 224| +------------------------------------+ SELECT datepart('SECONDS', timestamp'2019-10-01 00:00:01.000001'); +-------------------------------------------------------------+ |datepart(SECONDS FROM TIMESTAMP '2019-10-01 00:00:01.000001')| +-------------------------------------------------------------+ | 1.000001| +-------------------------------------------------------------+ SELECT datepart('days', interval 5 days 3 hours 7 minutes); +----------------------------------------------------+ |datepart(days FROM INTERVAL '5 03:07' DAY TO MINUTE)| +----------------------------------------------------+ | 5| +----------------------------------------------------+ SELECT datepart('seconds', interval 5 hours 30 seconds 1 milliseconds 1 microseconds); +----------------------------------------------------------------+ |datepart(seconds FROM INTERVAL '05:00:30.001001' HOUR TO SECOND)| +----------------------------------------------------------------+ | 30.001001| +----------------------------------------------------------------+ SELECT datepart('MONTH', INTERVAL '2021-11' YEAR TO MONTH); +-----------------------------------------------------+ |datepart(MONTH FROM INTERVAL '2021-11' YEAR TO MONTH)| +-----------------------------------------------------+ | 11| +-----------------------------------------------------+ SELECT datepart('MINUTE', INTERVAL '123 23:55:59.002001' DAY TO SECOND); +------------------------------------------------------------------+ |datepart(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND)| +------------------------------------------------------------------+ | 55| +------------------------------------------------------------------+ -- day SELECT day('2009-07-30'); +---------------+ |day(2009-07-30)| +---------------+ | 30| +---------------+ -- dayofmonth SELECT dayofmonth('2009-07-30'); +----------------------+ |dayofmonth(2009-07-30)| +----------------------+ | 30| +----------------------+ -- dayofweek SELECT dayofweek('2009-07-30'); +---------------------+ |dayofweek(2009-07-30)| +---------------------+ | 5| +---------------------+ -- dayofyear SELECT dayofyear('2016-04-09'); +---------------------+ |dayofyear(2016-04-09)| +---------------------+ | 100| +---------------------+ -- extract SELECT extract(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456'); +---------------------------------------------------------+ |extract(YEAR FROM TIMESTAMP '2019-08-12 01:00:00.123456')| +---------------------------------------------------------+ | 2019| +---------------------------------------------------------+ SELECT extract(week FROM timestamp'2019-08-12 01:00:00.123456'); +---------------------------------------------------------+ |extract(week FROM TIMESTAMP '2019-08-12 01:00:00.123456')| +---------------------------------------------------------+ | 33| +---------------------------------------------------------+ SELECT extract(doy FROM DATE'2019-08-12'); +-----------------------------------+ |extract(doy FROM DATE '2019-08-12')| +-----------------------------------+ | 224| +-----------------------------------+ SELECT extract(SECONDS FROM timestamp'2019-10-01 00:00:01.000001'); +------------------------------------------------------------+ |extract(SECONDS FROM TIMESTAMP '2019-10-01 00:00:01.000001')| +------------------------------------------------------------+ | 1.000001| +------------------------------------------------------------+ SELECT extract(days FROM interval 5 days 3 hours 7 minutes); +---------------------------------------------------+ |extract(days FROM INTERVAL '5 03:07' DAY TO MINUTE)| +---------------------------------------------------+ | 5| +---------------------------------------------------+ SELECT extract(seconds FROM interval 5 hours 30 seconds 1 milliseconds 1 microseconds); +---------------------------------------------------------------+ |extract(seconds FROM INTERVAL '05:00:30.001001' HOUR TO SECOND)| +---------------------------------------------------------------+ | 30.001001| +---------------------------------------------------------------+ SELECT extract(MONTH FROM INTERVAL '2021-11' YEAR TO MONTH); +----------------------------------------------------+ |extract(MONTH FROM INTERVAL '2021-11' YEAR TO MONTH)| +----------------------------------------------------+ | 11| +----------------------------------------------------+ SELECT extract(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND); +-----------------------------------------------------------------+ |extract(MINUTE FROM INTERVAL '123 23:55:59.002001' DAY TO SECOND)| +-----------------------------------------------------------------+ | 55| +-----------------------------------------------------------------+ -- from_unixtime SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss'); +-------------------------------------+ |from_unixtime(0, yyyy-MM-dd HH:mm:ss)| +-------------------------------------+ | 1970-01-01 09:00:00| +-------------------------------------+ SELECT from_unixtime(0); +-------------------------------------+ |from_unixtime(0, yyyy-MM-dd HH:mm:ss)| +-------------------------------------+ | 1970-01-01 09:00:00| +-------------------------------------+ -- from_utc_timestamp SELECT from_utc_timestamp('2016-08-31', 'Asia/Seoul'); +------------------------------------------+ |from_utc_timestamp(2016-08-31, Asia/Seoul)| +------------------------------------------+ | 2016-08-31 09:00:00| +------------------------------------------+ -- hour SELECT hour('2009-07-30 12:58:59'); +-------------------------+ |hour(2009-07-30 12:58:59)| +-------------------------+ | 12| +-------------------------+ -- last_day SELECT last_day('2009-01-12'); +--------------------+ |last_day(2009-01-12)| +--------------------+ | 2009-01-31| +--------------------+ -- localtimestamp SELECT localtimestamp(); +--------------------+ | localtimestamp()| +--------------------+ |2024-02-24 16:36:...| +--------------------+ -- make_date SELECT make_date(2013, 7, 15); +----------------------+ |make_date(2013, 7, 15)| +----------------------+ | 2013-07-15| +----------------------+ SELECT make_date(2019, 7, NULL); +------------------------+ |make_date(2019, 7, NULL)| +------------------------+ | NULL| +------------------------+ -- make_dt_interval SELECT make_dt_interval(1, 12, 30, 01.001001); +-------------------------------------+ |make_dt_interval(1, 12, 30, 1.001001)| +-------------------------------------+ | INTERVAL '1 12:30...| +-------------------------------------+ SELECT make_dt_interval(2); +-----------------------------------+ |make_dt_interval(2, 0, 0, 0.000000)| +-----------------------------------+ | INTERVAL '2 00:00...| +-----------------------------------+ SELECT make_dt_interval(100, null, 3); +----------------------------------------+ |make_dt_interval(100, NULL, 3, 0.000000)| +----------------------------------------+ | NULL| +----------------------------------------+ -- make_interval SELECT make_interval(100, 11, 1, 1, 12, 30, 01.001001); +----------------------------------------------+ |make_interval(100, 11, 1, 1, 12, 30, 1.001001)| +----------------------------------------------+ | 100 years 11 mont...| +----------------------------------------------+ SELECT make_interval(100, null, 3); +----------------------------------------------+ |make_interval(100, NULL, 3, 0, 0, 0, 0.000000)| +----------------------------------------------+ | NULL| +----------------------------------------------+ SELECT make_interval(0, 1, 0, 1, 0, 0, 100.000001); +-------------------------------------------+ |make_interval(0, 1, 0, 1, 0, 0, 100.000001)| +-------------------------------------------+ | 1 months 1 days 1...| +-------------------------------------------+ -- make_timestamp SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887); +-------------------------------------------+ |make_timestamp(2014, 12, 28, 6, 30, 45.887)| +-------------------------------------------+ | 2014-12-28 06:30:...| +-------------------------------------------+ SELECT make_timestamp(2014, 12, 28, 6, 30, 45.887, 'CET'); +------------------------------------------------+ |make_timestamp(2014, 12, 28, 6, 30, 45.887, CET)| +------------------------------------------------+ | 2014-12-28 14:30:...| +------------------------------------------------+ SELECT make_timestamp(2019, 6, 30, 23, 59, 60); +---------------------------------------+ |make_timestamp(2019, 6, 30, 23, 59, 60)| +---------------------------------------+ | 2019-07-01 00:00:00| +---------------------------------------+ SELECT make_timestamp(2019, 6, 30, 23, 59, 1); +--------------------------------------+ |make_timestamp(2019, 6, 30, 23, 59, 1)| +--------------------------------------+ | 2019-06-30 23:59:01| +--------------------------------------+ SELECT make_timestamp(null, 7, 22, 15, 30, 0); +--------------------------------------+ |make_timestamp(NULL, 7, 22, 15, 30, 0)| +--------------------------------------+ | NULL| +--------------------------------------+ -- make_timestamp_ltz SELECT make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887); +-----------------------------------------------+ |make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887)| +-----------------------------------------------+ | 2014-12-28 06:30:...| +-----------------------------------------------+ SELECT make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887, 'CET'); +----------------------------------------------------+ |make_timestamp_ltz(2014, 12, 28, 6, 30, 45.887, CET)| +----------------------------------------------------+ | 2014-12-28 14:30:...| +----------------------------------------------------+ SELECT make_timestamp_ltz(2019, 6, 30, 23, 59, 60); +-------------------------------------------+ |make_timestamp_ltz(2019, 6, 30, 23, 59, 60)| +-------------------------------------------+ | 2019-07-01 00:00:00| +-------------------------------------------+ SELECT make_timestamp_ltz(null, 7, 22, 15, 30, 0); +------------------------------------------+ |make_timestamp_ltz(NULL, 7, 22, 15, 30, 0)| +------------------------------------------+ | NULL| +------------------------------------------+ -- make_timestamp_ntz SELECT make_timestamp_ntz(2014, 12, 28, 6, 30, 45.887); +-----------------------------------------------+ |make_timestamp_ntz(2014, 12, 28, 6, 30, 45.887)| +-----------------------------------------------+ | 2014-12-28 06:30:...| +-----------------------------------------------+ SELECT make_timestamp_ntz(2019, 6, 30, 23, 59, 60); +-------------------------------------------+ |make_timestamp_ntz(2019, 6, 30, 23, 59, 60)| +-------------------------------------------+ | 2019-07-01 00:00:00| +-------------------------------------------+ SELECT make_timestamp_ntz(null, 7, 22, 15, 30, 0); +------------------------------------------+ |make_timestamp_ntz(NULL, 7, 22, 15, 30, 0)| +------------------------------------------+ | NULL| +------------------------------------------+ -- make_ym_interval SELECT make_ym_interval(1, 2); +----------------------+ |make_ym_interval(1, 2)| +----------------------+ | INTERVAL '1-2' YE...| +----------------------+ SELECT make_ym_interval(1, 0); +----------------------+ |make_ym_interval(1, 0)| +----------------------+ | INTERVAL '1-0' YE...| +----------------------+ SELECT make_ym_interval(-1, 1); +-----------------------+ |make_ym_interval(-1, 1)| +-----------------------+ | INTERVAL '-0-11' ...| +-----------------------+ SELECT make_ym_interval(2); +----------------------+ |make_ym_interval(2, 0)| +----------------------+ | INTERVAL '2-0' YE...| +----------------------+ -- minute SELECT minute('2009-07-30 12:58:59'); +---------------------------+ |minute(2009-07-30 12:58:59)| +---------------------------+ | 58| +---------------------------+ -- month SELECT month('2016-07-30'); +-----------------+ |month(2016-07-30)| +-----------------+ | 7| +-----------------+ -- months_between SELECT months_between('1997-02-28 10:30:00', '1996-10-30'); +-----------------------------------------------------+ |months_between(1997-02-28 10:30:00, 1996-10-30, true)| +-----------------------------------------------------+ | 3.94959677| +-----------------------------------------------------+ SELECT months_between('1997-02-28 10:30:00', '1996-10-30', false); +------------------------------------------------------+ |months_between(1997-02-28 10:30:00, 1996-10-30, false)| +------------------------------------------------------+ | 3.9495967741935485| +------------------------------------------------------+ -- next_day SELECT next_day('2015-01-14', 'TU'); +------------------------+ |next_day(2015-01-14, TU)| +------------------------+ | 2015-01-20| +------------------------+ -- now SELECT now(); +--------------------+ | now()| +--------------------+ |2024-02-24 16:36:...| +--------------------+ -- quarter SELECT quarter('2016-08-31'); +-------------------+ |quarter(2016-08-31)| +-------------------+ | 3| +-------------------+ -- second SELECT second('2009-07-30 12:58:59'); +---------------------------+ |second(2009-07-30 12:58:59)| +---------------------------+ | 59| +---------------------------+ -- session_window SELECT a, session_window.start, session_window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:10:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, session_window(b, '5 minutes') ORDER BY a, start; +---+-------------------+-------------------+---+ | a| start| end|cnt| +---+-------------------+-------------------+---+ | A1|2021-01-01 00:00:00|2021-01-01 00:09:30| 2| | A1|2021-01-01 00:10:00|2021-01-01 00:15:00| 1| | A2|2021-01-01 00:01:00|2021-01-01 00:06:00| 1| +---+-------------------+-------------------+---+ SELECT a, session_window.start, session_window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:10:00'), ('A2', '2021-01-01 00:01:00'), ('A2', '2021-01-01 00:04:30') AS tab(a, b) GROUP by a, session_window(b, CASE WHEN a = 'A1' THEN '5 minutes' WHEN a = 'A2' THEN '1 minute' ELSE '10 minutes' END) ORDER BY a, start; +---+-------------------+-------------------+---+ | a| start| end|cnt| +---+-------------------+-------------------+---+ | A1|2021-01-01 00:00:00|2021-01-01 00:09:30| 2| | A1|2021-01-01 00:10:00|2021-01-01 00:15:00| 1| | A2|2021-01-01 00:01:00|2021-01-01 00:02:00| 1| | A2|2021-01-01 00:04:30|2021-01-01 00:05:30| 1| +---+-------------------+-------------------+---+ -- timestamp_micros SELECT timestamp_micros(1230219000123123); +----------------------------------+ |timestamp_micros(1230219000123123)| +----------------------------------+ | 2008-12-26 00:30:...| +----------------------------------+ -- timestamp_millis SELECT timestamp_millis(1230219000123); +-------------------------------+ |timestamp_millis(1230219000123)| +-------------------------------+ | 2008-12-26 00:30:...| +-------------------------------+ -- timestamp_seconds SELECT timestamp_seconds(1230219000); +-----------------------------+ |timestamp_seconds(1230219000)| +-----------------------------+ | 2008-12-26 00:30:00| +-----------------------------+ SELECT timestamp_seconds(1230219000.123); +---------------------------------+ |timestamp_seconds(1230219000.123)| +---------------------------------+ | 2008-12-26 00:30:...| +---------------------------------+ -- to_date SELECT to_date('2009-07-30 04:17:52'); +----------------------------+ |to_date(2009-07-30 04:17:52)| +----------------------------+ | 2009-07-30| +----------------------------+ SELECT to_date('2016-12-31', 'yyyy-MM-dd'); +-------------------------------+ |to_date(2016-12-31, yyyy-MM-dd)| +-------------------------------+ | 2016-12-31| +-------------------------------+ -- to_timestamp SELECT to_timestamp('2016-12-31 00:12:00'); +---------------------------------+ |to_timestamp(2016-12-31 00:12:00)| +---------------------------------+ | 2016-12-31 00:12:00| +---------------------------------+ SELECT to_timestamp('2016-12-31', 'yyyy-MM-dd'); +------------------------------------+ |to_timestamp(2016-12-31, yyyy-MM-dd)| +------------------------------------+ | 2016-12-31 00:00:00| +------------------------------------+ -- to_timestamp_ltz SELECT to_timestamp_ltz('2016-12-31 00:12:00'); +-------------------------------------+ |to_timestamp_ltz(2016-12-31 00:12:00)| +-------------------------------------+ | 2016-12-31 00:12:00| +-------------------------------------+ SELECT to_timestamp_ltz('2016-12-31', 'yyyy-MM-dd'); +----------------------------------------+ |to_timestamp_ltz(2016-12-31, yyyy-MM-dd)| +----------------------------------------+ | 2016-12-31 00:00:00| +----------------------------------------+ -- to_timestamp_ntz SELECT to_timestamp_ntz('2016-12-31 00:12:00'); +-------------------------------------+ |to_timestamp_ntz(2016-12-31 00:12:00)| +-------------------------------------+ | 2016-12-31 00:12:00| +-------------------------------------+ SELECT to_timestamp_ntz('2016-12-31', 'yyyy-MM-dd'); +----------------------------------------+ |to_timestamp_ntz(2016-12-31, yyyy-MM-dd)| +----------------------------------------+ | 2016-12-31 00:00:00| +----------------------------------------+ -- to_unix_timestamp SELECT to_unix_timestamp('2016-04-08', 'yyyy-MM-dd'); +-----------------------------------------+ |to_unix_timestamp(2016-04-08, yyyy-MM-dd)| +-----------------------------------------+ | 1460041200| +-----------------------------------------+ -- to_utc_timestamp SELECT to_utc_timestamp('2016-08-31', 'Asia/Seoul'); +----------------------------------------+ |to_utc_timestamp(2016-08-31, Asia/Seoul)| +----------------------------------------+ | 2016-08-30 15:00:00| +----------------------------------------+ -- trunc SELECT trunc('2019-08-04', 'week'); +-----------------------+ |trunc(2019-08-04, week)| +-----------------------+ | 2019-07-29| +-----------------------+ SELECT trunc('2019-08-04', 'quarter'); +--------------------------+ |trunc(2019-08-04, quarter)| +--------------------------+ | 2019-07-01| +--------------------------+ SELECT trunc('2009-02-12', 'MM'); +---------------------+ |trunc(2009-02-12, MM)| +---------------------+ | 2009-02-01| +---------------------+ SELECT trunc('2015-10-27', 'YEAR'); +-----------------------+ |trunc(2015-10-27, YEAR)| +-----------------------+ | 2015-01-01| +-----------------------+ -- try_to_timestamp SELECT try_to_timestamp('2016-12-31 00:12:00'); +-------------------------------------+ |try_to_timestamp(2016-12-31 00:12:00)| +-------------------------------------+ | 2016-12-31 00:12:00| +-------------------------------------+ SELECT try_to_timestamp('2016-12-31', 'yyyy-MM-dd'); +----------------------------------------+ |try_to_timestamp(2016-12-31, yyyy-MM-dd)| +----------------------------------------+ | 2016-12-31 00:00:00| +----------------------------------------+ SELECT try_to_timestamp('foo', 'yyyy-MM-dd'); +---------------------------------+ |try_to_timestamp(foo, yyyy-MM-dd)| +---------------------------------+ | NULL| +---------------------------------+ -- unix_date SELECT unix_date(DATE("1970-01-02")); +---------------------+ |unix_date(1970-01-02)| +---------------------+ | 1| +---------------------+ -- unix_micros SELECT unix_micros(TIMESTAMP('1970-01-01 00:00:01Z')); +---------------------------------+ |unix_micros(1970-01-01 00:00:01Z)| +---------------------------------+ | 1000000| +---------------------------------+ -- unix_millis SELECT unix_millis(TIMESTAMP('1970-01-01 00:00:01Z')); +---------------------------------+ |unix_millis(1970-01-01 00:00:01Z)| +---------------------------------+ | 1000| +---------------------------------+ -- unix_seconds SELECT unix_seconds(TIMESTAMP('1970-01-01 00:00:01Z')); +----------------------------------+ |unix_seconds(1970-01-01 00:00:01Z)| +----------------------------------+ | 1| +----------------------------------+ -- unix_timestamp SELECT unix_timestamp(); +--------------------------------------------------------+ |unix_timestamp(current_timestamp(), yyyy-MM-dd HH:mm:ss)| +--------------------------------------------------------+ | 1708760216| +--------------------------------------------------------+ SELECT unix_timestamp('2016-04-08', 'yyyy-MM-dd'); +--------------------------------------+ |unix_timestamp(2016-04-08, yyyy-MM-dd)| +--------------------------------------+ | 1460041200| +--------------------------------------+ -- weekday SELECT weekday('2009-07-30'); +-------------------+ |weekday(2009-07-30)| +-------------------+ | 3| +-------------------+ -- weekofyear SELECT weekofyear('2008-02-20'); +----------------------+ |weekofyear(2008-02-20)| +----------------------+ | 8| +----------------------+ -- window SELECT a, window.start, window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '5 minutes') ORDER BY a, start; +---+-------------------+-------------------+---+ | a| start| end|cnt| +---+-------------------+-------------------+---+ | A1|2021-01-01 00:00:00|2021-01-01 00:05:00| 2| | A1|2021-01-01 00:05:00|2021-01-01 00:10:00| 1| | A2|2021-01-01 00:00:00|2021-01-01 00:05:00| 1| +---+-------------------+-------------------+---+ SELECT a, window.start, window.end, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '10 minutes', '5 minutes') ORDER BY a, start; +---+-------------------+-------------------+---+ | a| start| end|cnt| +---+-------------------+-------------------+---+ | A1|2020-12-31 23:55:00|2021-01-01 00:05:00| 2| | A1|2021-01-01 00:00:00|2021-01-01 00:10:00| 3| | A1|2021-01-01 00:05:00|2021-01-01 00:15:00| 1| | A2|2020-12-31 23:55:00|2021-01-01 00:05:00| 1| | A2|2021-01-01 00:00:00|2021-01-01 00:10:00| 1| +---+-------------------+-------------------+---+ -- window_time SELECT a, window.start as start, window.end as end, window_time(window), cnt FROM (SELECT a, window, count(*) as cnt FROM VALUES ('A1', '2021-01-01 00:00:00'), ('A1', '2021-01-01 00:04:30'), ('A1', '2021-01-01 00:06:00'), ('A2', '2021-01-01 00:01:00') AS tab(a, b) GROUP by a, window(b, '5 minutes') ORDER BY a, window.start); +---+-------------------+-------------------+--------------------+---+ | a| start| end| window_time(window)|cnt| +---+-------------------+-------------------+--------------------+---+ | A1|2021-01-01 00:00:00|2021-01-01 00:05:00|2021-01-01 00:04:...| 2| | A1|2021-01-01 00:05:00|2021-01-01 00:10:00|2021-01-01 00:09:...| 1| | A2|2021-01-01 00:00:00|2021-01-01 00:05:00|2021-01-01 00:04:...| 1| +---+-------------------+-------------------+--------------------+---+ -- year SELECT year('2016-07-30'); +----------------+ |year(2016-07-30)| +----------------+ | 2016| +----------------+
