磐维数据库中使用日期的四种方式

本文将介绍在磐维数据库中如何使用日期来查询数据。

interval间隔类型

第一种比较常见的使用方式是interval，interval类型支持对时间进行运算，非常方便。

例如查询过去30天的订单总额，可以使用间隔类型

sysdate - INTERVAL '30 days'

参考示例语句如下：

SELECT sum(amount) 
  FROM orders
 WHERE order_date >= sysdate - INTERVAL '30 days';

也可以修改sysdate为current_date返回当前日期而不包含时间。

SELECT sum(amount) 
  FROM orders
 WHERE order_date >= current_date - INTERVAL '30 days';

再稍微复杂一点就是间隔范围查询，例如将订单按时间范围分类为：“30-60天前”、“60-90天前”

SELECT
    CASE
        WHEN order_date BETWEEN (sysdate - INTERVAL '60 days') AND (sysdate - INTERVAL '30 days')
            THEN '30-60 days ago'
        WHEN order_date BETWEEN (sysdate - INTERVAL '90 days') AND (sysdate - INTERVAL '60 days')
            THEN '60-90 days ago'
    END AS date_range,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales
FROM
  orders
WHERE
  order_date BETWEEN (sysdate - INTERVAL '90 days') AND (sysdate - INTERVAL '30 days')
GROUP BY date_range
ORDER BY date_range;

   date_range   | total_orders | total_sales
----------------+--------------+-------------
 30-60 days ago |          239 |   321653.81
 60-90 days ago |          202 |    74235.42

date_trunc按预定格式截断

使用date_trunc函数可以非常便捷地对时间类型按按日、周、月、季度和年预定格式进行截断。

/* 本月的头一天 */
SELECT date_trunc('month', current_date);

/* 本周的头一天 */
SELECT date_trunc('week', current_date);

/* 本年的头一天 */
SELECT date_trunc('year', current_date);

/* 本季度的头一天 */
SELECT date_trunc('quarter', current_date);

例如按月份显示订单数量和总订单数量：

SELECT
  date_trunc ('month', order_date) AS month,
  COUNT(*) AS total_orders,
  SUM(amount) AS monthly_total
FROM orders
GROUP BY 1
ORDER BY month;

        month        | total_orders | monthly_total
---------------------+--------------+---------------
 2024-09-01 00:00:00 |           21 |       3276.21
 2024-10-01 00:00:00 |           49 |       6753.18
(2 rows)

date_trunc函数支持的预定义格式包括：millennium、century、decade、year、quarter、week、day、hour、minute、second、millisecond

extract函数

extract函数可以提取日期或时间类型的特定值。例如，日、月、年、时、分、秒等值。

SELECT extract('epoch' from sysdate);
 date_part  
------------
 1730346050
(1 row)

SELECT extract('hour' from now());
 extract 
---------
       3
(1 row)

to_char日期格式转换函数

to_char函数的示例如下：

SELECT to_char(now(),'yyyymmdd hh24:mi:ss.ff7');

          to_char          
--------------------------
 20241031 03:56:49.4690947
(1 row)

输出四位年、两位月份、两位日，二十四进制的时分秒，七位精度的毫秒值。

总结

本文介绍了使用interval对日期进行加减修改等运算、使用date_trunc对日期时间进行截断，使用extract函数进行特定值提取，使用to_char函数对日期格式或文本字符串进行格式转换。