GaussDB 操作时间【玩转PB级数仓GaussDB(DWS)】

前言在使用 GaussDB DWS 进行数仓相关操作时我们经常会涉及到对时间的操作本文主要来讲解下有关时间操作的定义及函数格式化字符串模式描述HH一天的小时数(01-12)HH12一天的小时数(01-12)HH24一天的小时数(00-23)MI分钟(00-59)ss秒(00-59)MS毫秒(000-999)US微秒(000000-999999)AM或A.M.正午标识大写pm或p.m.下午标识小写Y,YYY带逗号的年(4和更多位)YYYY年(4和更多位)YYY年的后三位YY年的后两位Y年的最后一位BC或B.C. 或 AD 或 A.D.纪元标识(大写)CC世纪(2位)MONTH全长大写月份名(空白填充为9字符)Month全长混合大小写月份名(空白填充为9字符)month全长小写月份名(空白填充为9字符)MON大写缩写月份名(3字符)Mon缩写混合大小写月份名(3字符)mon小写缩写月份名(3字符)MM月份号(01-12)DAY全长大写日期名(空白填充为9字符)Day全长混合大小写日期名(空白填充为9字符)day全长小写日期名(空白填充为9字符)DY缩写大写日期名(3字符)Dy缩写混合大小写日期名(3字符)dy缩写小写日期名(3字符)DDD一年里的日子(001-366)DD一个月里的日子(01-31)D一周里的日子(1-7周日是1)W一个月里的周数(1-5)第一周从该月第一天开始WW一年里的周数(1-53)第一周从该年第一天开始算IWISO一年里的咒术(第一个星期四在第一周里)Q季度RM罗马数字的月份(I-XII I1月)(大写)rm罗马数字的月份(I-XII I1月)(小写)TZ时区名(大写)tz时区名(小写)时间加减操作符例子结果date ‘2022-09-28’ integer ‘7’date ‘2022-10-05’date ‘2022-09-28’ interval ‘1 hour’timestamp ‘2022-09-28 01:00:00’date ‘2022-09-28’ time ‘03:00’timestamp ‘2022-09-28 03:00:00’interval ‘1 day’ interval ‘1 hour’interval ‘1 day 01:00:00’timestamp ‘2022-09-28 01:00’ interval ‘23 hours’timestamp ‘2022-09-29 00:00:00’time ‘01:00’ interval ‘3 hours’time ‘04:00:00’-- interval ‘23 hours’interval ‘-23:00:00’-date ‘2022-10-01’ - date ‘2022-09-28’integer ‘3’ (days)-date ‘2022-10-01’ - integer ‘7’date ‘2022-09-24’-date ‘2022-09-28’ - interval ‘1 hour’timestamp ‘2022-09-27 23:00:00’-time ‘05:00’ - time ‘03:00’interval ‘02:00:00’-time ‘05:00’ - interval ‘2 hours’time ‘03:00:00’-timestamp ‘2022-09-28 23:00’ - interval ‘23 hours’timestamp ‘2022-09-28 00:00:00’-interval ‘1 day’ - interval ‘1 hour’interval ‘1 day -01:00:00’-timestamp ‘2022-09-29 03:00’ - timestamp ‘2022-09-27 12:00’interval ‘1 day 15:00:00’*900 * interval ‘1 second’interval ‘00:15:00’*21 * interval ‘1 day’interval ‘21 days’*double precision ‘3.5’ * interval ‘1 hour’interval ‘03:30:00’/interval ‘1 hour’ / double precision ‘1.5’interval ‘00:40:00’字符串与日期、时间戳的转换将字符串类型转为日期类型select to_date(2022-06-14,yyyy-mm-dd); select to_date(14 Jun 2022,DD Mon yyyy);将字符串类型转为时间戳select to_timestamp(1655211000); select to_timestamp(2022-06-04, yyyy-mm-dd);时间戳、日期类型转字符串将时间戳转为字符串(年月日)select to_char(now(),yyyy-mm-dd);将时间戳转为字符串(年月日 时分秒)select to_char(now(),yyyy-mm-dd HH24:MI:SS);将时间间隔转为字符串select to_char(interval 1 year 1 mon 2day 3h 4m 5s,yy-mm-dd HH24:MI:SS);将日期转为字符串select cast(current_date as VARCHAR); select to_char(CURRENT_DATE,yy-mm-dd);日期与时间戳之间的互相转换带时区(推荐使用)select EXTRACT(epoch FROM CAST(CURRENT_DATE AS TIMESTAMPTZ));不带时区(会多出8小时时间会变为08:00:00)select EXTRACT(epoch from CURRENT_DATE); select EXTRACT(epoch FROM CAST(CURRENT_DATE AS TIMESTAMP));时间、日期筛选为方便进行演示在这里进行构造数据。DROP TABLE IF EXISTS test_table; CREATE TABLE test_table ( depart varchar(15), name varchar(15), create_date date, last_login_date varchar(50), update_time timestamp, birth_time varchar(50), salary int8 ) ; INSERT INTO test_table (depart, name, create_date,last_login_date,update_time,birth_time,salary) WITH test_table AS ( SELECT UNNEST( ARRAY [ 财务, 行政, 销售, 财务, 行政, 行政 ] ) AS depart, UNNEST ( ARRAY [ A, B, A, C, D, C ] ) AS NAME, UNNEST ( ARRAY [ cast(2022-12-21 as date), cast(2021-01-03 as date), cast(2023-01-01 as date), cast(2022-05-31 as date), cast(2021-02-28 as date), cast(2019-01-31 as date) ] ) AS create_date, UNNEST ( ARRAY [ 2023-06-21, 2023-06-03, 2023-06-01, 2022-06-02, 2023-06-28, 2023-05-31 ] ) AS last_login_date, UNNEST ( ARRAY [to_timestamp(2023-05-21 12:20:30,yyyy-mm-dd HH24:MI:SS), to_timestamp(2023-01-03 23:59:58,yyyy-mm-dd HH24:MI:SS), to_timestamp(2023-01-04 01:01:39,yyyy-mm-dd HH24:MI:SS), to_timestamp(2023-01-01 11:32:36,yyyy-mm-dd HH24:MI:SS), to_timestamp(2022-12-31 09:18:27,yyyy-mm-dd HH24:MI:SS), to_timestamp(2022-12-30 17:12:29,yyyy-mm-dd HH24:MI:SS)] ) AS update_time, UNNEST ( ARRAY [ 1992-02-11 09:21:36, 1996-05-03 21:59:51, 1994-05-24 18:09:14, 1990-03-03 05:12:43, 1993-12-31 15:22:57, 1997-11-30 10:10:49]) AS birth_time, UNNEST ( ARRAY [ 200, 100, 50, 30, 200, 100 ] ) AS donate ) SELECT * FROM test_table ;以下基于上面的数据进行日期筛选对字符串格式/日期格式进行筛选获取last_login_date大于等于2022-06-21日的select * from test_table where last_login_date2022-06-21;获取create_date大于等于2021-05-21日的select * from test_table where create_date2021-05-21;获取last_login_date等于2022-06-21日的select * from test_table where last_login_date2022-06-21 and last_login_date 2022-06-21; select * from test_table where last_login_date between 2022-06-21 and 2022-06-21;获取last_login_date大于等于2021-05-21日并且小于等于2021-12-10日的select * from test_table where create_date2021-05-21 and create_date2021-12-10; select * from test_table where create_date between 2021-05-21 and 2021-12-10;基于字符串格式/时间戳格式的时间筛选获取出生日期大于等于1994-01-03的数据select * from test_table where birth_time1994-01-03;获取出生日期在1994-01-03和1994-01-04日期之间的数据含1994-01-03和1994-01-04当天的数据。特别注意以下写法会遗漏1994-01-04当天的数据。select * from test_table where birth_time between 1994-01-03 and 1994-01-04;需要对末尾日期加1天等同于‘1994-01-03 00:00:00’ and’1994-01-05 00:00:00’select * from test_table where birth_time between 1994-01-03 and 1994-01-05;需要对日期进行转换select * from test_table where cast(birth_time as date) between 1994-01-03 and 1994-01-04;需要加上时分秒select * from test_table where birth_time 1994-01-03 00:00:00 and birth_time 1994-01-04 23:59:59; select * from test_table where birth_time between 1994-01-03 00:00:00 and 1994-01-04 23:59:59;基于时间戳格式的日期筛选与字符串格式的筛选方法类似这里不多加赘述时间截断按年截断结果为2022-01-01 00:00:00SELECT date_trunc(year, TIMESTAMP 2022-06-14 20:24:30);按月截断结果为2022-06-01 00:00:00SELECT date_trunc(month, TIMESTAMP 2022-06-14 20:24:30);按天截断结果为2022-06-14 00:00:0008SELECT date_trunc(day, TIMESTAMP WITH TIME ZONE 2022-06-14 20:24:3008);按天和时区截断结果为2022-06-14 22:00:0008SELECT date_trunc(day, TIMESTAMP WITH TIME ZONE 2022-06-14 20:24:3000, Australia/Sydney);按小时截断结果为2022-06-14 20:00:00SELECT date_trunc(hour, TIMESTAMP 2022-06-14 20:24:30);按小时对时间间隔进行截断结果为2 days 02:00:00SELECT date_trunc(hour, INTERVAL 2 days 02:30:58);计算时间差with temp as (select now() now,now() -INTERVAL 2 year 2 month 1 day 1h 3m 33s last_date) select now, last_date, EXTRACT(year from age(now,last_date)) years, 年 EXTRACT(year from age(now,last_date))*12EXTRACT(month from age(now,last_date)) months, 月份 EXTRACT(day from (now-last_date)) days, 日 now::date-last_date::date days_2, 日 EXTRACT(epoch from (now-last_date)/3600) hours, 时 round(EXTRACT(epoch from (now-last_date))::numeric/3600,2) hour_2fs, 时 四舍五入形式 EXTRACT(epoch from (now-last_date))::numeric/60 minutes, 分 trunc(EXTRACT(epoch from (now-last_date))::numeric/60,1) minutes_1fs, 分 截断形式 round(EXTRACT(epoch from (now-last_date))::numeric/60,1) minutes_1fs_another, 分 四舍五入形式 EXTRACT(epoch from (now-last_date)) epoch 秒数 from temp;生成日期序列按天生成自2020-01-01至2023-12-31的时间序列SELECT generate_series(DATE 2020-01-01, DATE 2023-12-31, INTERVAL 1 day)::DATE AS frequency;