【Databand】日期时间函数

文章目录

- 获取当前日期和时间
- 日期格式化函数
- 日期加减运算
- 日期时间和时间戳转化
- 日期时间各部分拆分
- 日期时间加减运算
- 实际应用扩展
- 总结

获取当前日期和时间

Databend 使用 UTC 作为默认时区，并允许您将时区更改为当前地理位置。

-- 查看时区
select timezone();
+-----------------+
| timezone()      |
+-----------------+
| UTC             |
+-----------------+
-- 修改时区
set timezone='asia/shanghai';

Databend 常见的获取当前日期和时间函数如下：

now() 返回 timestamp 数据类型，以“YYYY-MM-DD hh:mm:ss.fff”格式返回当前日期和时间。
today() 返回 date 数据类型，以“YYYY-MM-DD”格式返回当前日期。
yesterday() 返回 date 数据类型，以“YYYY-MM-DD”格式返回昨天日期，与 today() - 1 相同。
tomorrow() 返回 date 数据类型，以“YYYY-MM-DD”格式返回明天日期，与 today() + 1 相同。

select now(),today(),yesterday(),tomorrow(),today()+1 as tomorrow;
+-------------------------+-------------+--------------+-------------+------------+
|           now()         |   today()   | yesterday()  |  tomorrow() |  tomorrow  |
+-------------------------+-------------+--------------+-------------+------------+
| 2024-01-08 22:19:55.188 | 2024-01-08  |  2024-01-07  |  2024-01-09 | 2024-01-09 |
+-------------------------+-------------+--------------+-------------+------------+

日期格式化函数

使用 to_date(expr[,format_text]) 可以将表达式转化为指定日期格式，转化为“YYYY-MM-DD”格式。如果给定两个参数，该函数会根据第二个字符串中指定的格式将第一个字符串转换为日期。语法和示例如下：

-- 语法
to_date(expr[,format_text]) 
-- 示列
select to_date('2023-12-13') as dt;-- 转化日期成功
select to_date('20231213') as dt;-- 报错，不能转化，格式不对
select to_date('20231213','%Y%m%d') as dt;-- 转化日期成功
select to_date('2023/12/13','%Y/%m/%d') as dt;-- 同理，需要指定格式才能转化日期成功
select to_date(null) as dt;-- 输出 NULL，但是不建议日期显示 NULL，因为会存在问题
select to_date(ifnull(null,1)) as dt;-- 优化后，输出 1970-01-02，
-- 如果同一列中日期存在多种格式怎么处理？
with t as
         (select '2023-12-13' as dt
          union all
          select '2023/12/13' as dt
          union all
          select '20231213' as dt
          union all
          select '2023/12/13 00:00:00' as dt
          union all
          select null as dt)
select dt,
       case
           when length(dt) = 8 then to_date(dt, '%Y%m%d')
           when length(dt) = 10 and dt like '%/%' then to_date(dt, '%Y/%m/%d')
           when length(dt) = 10 and dt like '%-%' then to_date(dt, '%Y-%m-%d')
           when length(dt) > 10 and dt like '%/%' then to_date(dt, '%Y/%m/%d %H:%M:%S')
           else to_date(ifnull(dt, 1)) end as d_std
from t;
+---------------------+------------+
|           dt        |   dt_std   |
+---------------------+------------+
|          NULL       | 1970-01-02 |
+---------------------+------------+
| 2023/12/13 00:00:00 | 2023-12-13 |
+---------------------+------------+
|        20231213     | 2023-12-13 |
+---------------------+------------+
|       2023/12/13    | 2023-12-13 |
+---------------------+------------+
|       2023-12-13    | 1970-01-02 |
+---------------------+------------+

上面存在多种格式处理过程中，如果对 NULL 不进行处理，可能会得不出来结果，或者得出的结果显示错误，如下图：
在这里插入图片描述

由此可见，Databend 日期处理函数语法和 Mysql 差异还是很大的，对于 Mysql 上面不管任何格式的日期，只要 date() 函数就能统一规范处理，而 Databend 则要针对不同的格式写不同的处理方式，这说明 Databend 对数据类型要求非常严格，在任何时候，不管是数据接入人员，亦或是数据开发人员，在建表过程中，设计字段都应指定准确的数据类型。

使用 date_formt() 函数也可以格式化日期，但是只能格式化数据类型为date的表达式，即将日期值转换为特定的字符串格式。语法和示例如下：

-- 语法
date_format(<date>, <format>)
-- 示例
select date_format('20231213','%Y/%m/%d');-- 报错，原因是20231213不是日期类型！
select date_format(to_date('20231213','%Y%m%d'),'%Y/%m/%d');-- 输出 2023/12/13

日期加减运算

使用 date_add() 函数对日期进行添加运算，返回与<date_or_time_expr>参数类型相同的值。语法：

date_add(<unit>, <value>,  <date_or_time_expr>)

参数解释如下：

：必须具有以下值 year、quarter、month、day、hour、minute 和 second。
：添加的时间单位数，value 可以为负数，相当于 date_sub() 函数。
<date_or_time_expr>：date 或 timestamp 类型的值。

使用 date_sub() 函数对日期进行减少运算，返回与 <date_or_time_expr> 参数类型相同的值。语法参数和 date_add() 函数一致。

数据示例：

select date_add(year, -1, now())  as up_year_time
     , date_add(day, 1, today())  as up_day
     , date_add(day, -1, today()) as down_day
     , date_sub(day, 1, today())  as down_day
     , date_sub(day, -1, today()) as up_day
;
+--------------------------+------------+------------+------------+------------+
|      up_year_time        |   up_day   | down_day   | down_day   |   up_day   |
+--------------------------+------------+------------+------------+------------+
| 2023-01-09 07:29:00.980  | 2024-01-09 | 2024-01-07 | 2024-01-07 | 2024-01-09 |
+--------------------------+------------+------------+------------+------------+

根据定义和示例，date_add() 和 date_sub() 函数掌握使用一个即可。

使用 date_trunc() 函数将日期、时间或时间戳值截断到指定的精度。语法：

date_trunc(<precision>, <date_or_time_expr>)

参数解释如下：

<precision>：必须具有以下值 year、quarter、month、day、hour、minute 和 second。
<date_or_time_expr>：date 或 timestamp 类型的值。

数据示例：

select today()                                            as dt
     , date_trunc(month, today())                         as cur_month_begin_dt
     , date_add(month, 1, date_trunc(month, today()) - 1) as cur_month_end_dt
;
+------------+--------------------+------------------+
|      dt    | cur_month_begin_dt | cur_month_end_dt | 
+------------+--------------------+------------------+
| 2024-01-08 |     2024-01-01     |   2024-01-31     |
+------------+--------------------+------------------+

通过 date_trunc() 和 date_add() 函数搭配使用，可以很好地计算出月初和月末。

日期时间和时间戳转化

使用 to_timestamp() 函数返回格式为“YYYY-MM-DD hh:mm:ss.fff”格式的时间戳。如果给定的字符串与此格式匹配，但没有时间部分，则会自动扩展到此模式。填充值为0。如果给定Unix时间戳，也会转化为日期时间格式。

select now(),to_timestamp(now());-- 报错，原因是 to_timestamp() 只对字符串转化！！！
select now(),to_timestamp(now()::vrchar) as t1,to_timestamp(1) as t2;
+-------------------------+-------------------------+-------------------------+
|           now()         |           t1            |             t2          | 
+-------------------------+-------------------------+-------------------------+
| 2024-01-09 10:32:07.783 | 2024-01-09 10:32:07.783 | 1970-01-01 08:00:01.000 |
+-------------------------+-------------------------+-------------------------+

使用 to_unix_timestamp() 函数将日期/时间格式的时间戳转换为Unix时间戳格式。Unix时间戳表示自 1970年1月1日00:00:00 UTC 以来经过的秒数。

select now(),to_unix_timestamp(now()) as unix,to_timestamp(1704767748) as t1;
+-------------------------+------------+-------------------------+
|           now()         |    unix    |             t2          | 
+-------------------------+------------+-------------------------+
| 2024-01-09 10:38:12.318 | 1704767892 | 2024-01-09 10:35:48.000 |
+-------------------------+------------+-------------------------+

日期时间各部分拆分

使用 extract() 都可以检索出日期、时间或时间戳的指定部分，语法类似如下：

extract( year | quarter | month | week | day | hour | minute | second | dow | doy from <date_or_time_expr> )

其中，dow 表示一周的一天，1表示周一，7表示周日。doy 表示一年中的第几天。

除了上面两个函数外，Databend 还有一些函数也能实现效果，如下：

to_year()：将带有时间（时间戳/日期时间）的日期或日期转换为年份数字。
to_month()：将带有时间（时间戳/日期时间）的日期或日期转换为月份数字。
to_quarter()：将带有时间（时间戳/日期时间）的日期或日期转换为季度数字。
to_week_of_year()：计算给定日期一年内的周数。
to_day_of_month()：将带有时间的日期或日期（时间戳/日期时间）转换为包含月份天数（1-31）的UInt8数字。
to_day_of_week()：将带有时间的日期或日期（时间戳/日期时间）转换为包含一周中日数的UInt8数字（周一为1，周日为7）。
to_day_of_year()：将日期或带有时间的日期（时间戳/日期时间）转换为包含一年中一天数字的UInt16数字（1-366）。
to_hour()：将带有时间（时间戳/日期时间）的日期转换为包含24小时时间（0-23）小时数的UInt8数字。
to_minute()：将带有时间的日期（时间戳/日期时间）转换为包含小时分钟数（0-59）的UInt8数字。
to_second()：将带有时间（时间戳/日期时间）的日期转换为包含分钟中秒数（0-59）的UInt8数字。

数据示例：

select now()                      as now_dt
     , extract(year from now())   as year_dt
-- ,extract(quarter from now())
-- ,extract(week from now())
-- ,to_week_of_year(now())
     , extract(doy from now())    as day_of_year1
     , to_day_of_year(now())      as day_of_year2
     , extract(day from now())    as day_of_month
     , to_day_of_month(now())     as day_of_month2
     , extract(dow from now())    as day_of_week1
     , to_day_of_week(now())      as day_of_week2
     , extract(hour from now())   as hour_dt
     , extract(minute from now()) as minute_dt
     , extract(second from now()) as second_dt
;

其中，被注释掉的官网虽然有函数语法，但是实际应用还不支持，但并不影响。

Databend 还有些其他函数扩展，可能有时候能用上。

to_yyyymm()：将带有时间的日期或日期（时间戳/日期时间）转换为包含年份和月份编号的UInt32编号。
to_yyyymmdd()：将带有时间（时间戳/日期时间）的日期或日期转换为包含年份和月份编号（YYYY 10000 + MM 100 + DD）的UInt32数字。
to_yyyymmddhhmmss()：将带有时间（时间戳/日期时间）的日期或日期转换为包含年份和月份编号的UInt64数字（YYYY 10000000000 + MM 100000000 + DD 1000000 + hh 10000 + mm * 100 + ss）。

日期时间加减运算

前面介绍了 date_add() 和 date_sub() 函数，也可以使用他们对日期时间做加减运算。除此之外，Databend 有新的函数也可以适用，将时间间隔添加到日期或时间戳中，返回日期或时间戳类型的结果。

add_years()
add_quarters()
add_months()
add_days()
add_hours()
add_minutes()
add_seconds()

select add_years(now(), -1)
     , add_quarters(now(), 1)
     , add_months(now(), 1)
     , add_days(now(), 1)
     , add_hours(now(), 1)
     , add_minutes(now(), 1)
     , add_seconds(now(), 1)
     , date_add(year, 1, now())
     , date_add(quarter, 1, now())
     , date_add(month, 1, now())
     , date_add(day, 1, now())
     , date_add(hour, 1, now())
     , date_add(minute, 1, now())
     , date_add(second, 1, now())
;

根据示例可知，添加时间单位数可正数也可负数，其实 Databend 远不止这些函数，还有很多，但是我们只要选择最常用 date_add() 函数就能解决所有相关日期时间加减运算问题。

实际应用扩展

利用当前时间，自动生成文件名，减少手动书写带来的错误。

select concat('guanfa_wang_',LEFT(to_yyyymmddhhmmss(add_minutes(now(),15))::varchar,12),'.sql') as file_name;
+------------------------------+
|            file_name         |
+------------------------------+
| guanfa_wang_202401091336.sql |
+------------------------------+

计算时间间隔。

select cur_create_time
     , create_time
     , (to_unix_timestamp(cur_create_time) - to_unix_timestamp(create_time))           as second_gap
     , round((to_unix_timestamp(now()) - to_unix_timestamp(create_time)) / 60, 0)      as minute_gap
     , round((to_unix_timestamp(now()) - to_unix_timestamp(create_time)) / 60 / 60, 0) as hour_gap
from (select now()                     as cur_create_time
           , date_add(hour, -8, now()) as create_time) as t1;
+-------------------------+-------------------------+------------+------------+------------+
|    cur_create_time      |        create_time      | second_gap | minute_gap |  hour_gap  |   
+-------------------------+-------------------------+------------+------------+------------+
| 2024-01-09 13:37:12.408 | 2024-01-09 05:37:12.408 |    28800   |     480    |     8      |
+-------------------------+-------------------------+------------+------------+------------+

总结

本文基本覆盖了数据库中所有关于日期时间类计算，学习掌握常用的几种即可，函数都是定义出来的，主要是多去思考逻辑。如果你觉得掌握了以上函数应用，感兴趣的话可以操作实践一下 Databend 生成日期表，可参考 MySQL 日期表制作。

参考资料：

Databend Date & Time Functions：https://databend.rs/sql/sql-functions/datetime-functions/
MySQL 日期表制作：https://blog.csdn.net/weixin_50357986/article/details/133815732