前言

在使用 GaussDB DWS 进行数仓相关操作时，我们经常会涉及到对时间的操作，本文主要来讲解下有关时间操作的定义及函数

格式化字符串

时间加减

字符串与日期、时间戳的转换

将字符串类型转为日期类型

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_date>='2022-06-21';

获取create_date大于等于2021-05-21日的

select * from test_table where create_date>='2021-05-21';

获取last_login_date等于2022-06-21日的

select * from test_table where last_login_date>='2022-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_date>='2021-05-21' and create_date<='2021-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_time>='1994-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:00

SELECT date_trunc('year', TIMESTAMP '2022-06-14 20:24:30');

按月截断，结果为2022-06-01 00:00:00

SELECT date_trunc('month', TIMESTAMP '2022-06-14 20:24:30');

按天截断，结果为2022-06-14 00:00:00+08

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2022-06-14 20:24:30+08');

按天和时区截断，结果为2022-06-14 22:00:00+08

SELECT date_trunc('day', TIMESTAMP WITH TIME ZONE '2022-06-14 20:24:30+00', 'Australia/Sydney');

按小时截断，结果为2022-06-14 20:00:00

SELECT date_trunc('hour', TIMESTAMP '2022-06-14 20:24:30');

按小时对时间间隔进行截断，结果为2 days 02:00:00

SELECT date_trunc('hour', INTERVAL '2 days 02:30:58');

6.计算时间差

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))*12+EXTRACT(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;

【一起来玩转PB级数仓GaussDB(DWS)，分享你的技术经验与体验心得，赢开发者大礼包！】第19期有奖征文火热进行中！

此外，在云声平台提出您的宝贵建议，标题以【云驻计划-定向征文】开头，还有机会赢取额外奖励。