Greenplum 与 MySQL 数据库获取周几函数及函数结果保持一致的方法

2023-03-24 00:00:00 函数数据库专区获取周日

1.问题说明
项目有两个不同的平台分别使用 Greenplum 和 MySQL 数据库，但是这两个数据库的函数是不相同的，所以需要维护两套查询 SQL。

2.根据周几获取数据
2.1 原始函数结果
MySQL获取周几的函数为date_format( date, '%w')结果是周一到周日为：1，2，3，4，5，6，0

-- MySQL数据库
SELECT '周一' AS weekInfo, date_format( '2022-04-11 08:00:00', '%w') AS weekVal UNION ALL
SELECT '周二' AS weekInfo, date_format( '2022-04-12 08:00:00', '%w') AS weekVal UNION ALL
SELECT '周三' AS weekInfo, date_format( '2022-04-13 08:00:00', '%w') AS weekVal UNION ALL
SELECT '周四' AS weekInfo, date_format( '2022-04-14 08:00:00', '%w') AS weekVal UNION ALL
SELECT '周五' AS weekInfo, date_format( '2022-04-15 08:00:00', '%w') AS weekVal UNION ALL
SELECT '周六' AS weekInfo, date_format( '2022-04-16 08:00:00', '%w') AS weekVal UNION ALL
SELECT '周日' AS weekInfo, date_format( '2022-04-17 08:00:00', '%w') AS weekVal

Greenplum获取周几的函数为to_char( timestamp, 'd')结果是周一到周日为：2，3，4，5，6，1

-- Greenplum数据库
SELECT '周一' AS weekInfo, to_char( to_timestamp( '2022-04-11 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALL
SELECT '周二' AS weekInfo, to_char( to_timestamp( '2022-04-12 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALL
SELECT '周三' AS weekInfo, to_char( to_timestamp( '2022-04-13 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALL
SELECT '周四' AS weekInfo, to_char( to_timestamp( '2022-04-14 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALL
SELECT '周五' AS weekInfo, to_char( to_timestamp( '2022-04-15 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALL
SELECT '周六' AS weekInfo, to_char( to_timestamp( '2022-04-16 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALL
SELECT '周日' AS weekInfo, to_char( to_timestamp( '2022-04-17 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal

2.2 实现函数结果一致
可以看到 MySQL 的函数获取的结果是数值，将结果+1即可实现与 Greenplum 数据库函数结果一致。

-- MySQL数据库
SELECT '周一' AS weekInfo, date_format( '2022-04-11 08:00:00', '%w') +1 AS weekVal UNION ALL
SELECT '周二' AS weekInfo, date_format( '2022-04-12 08:00:00', '%w') +1 AS weekVal UNION ALL
SELECT '周三' AS weekInfo, date_format( '2022-04-13 08:00:00', '%w') +1 AS weekVal UNION ALL
SELECT '周四' AS weekInfo, date_format( '2022-04-14 08:00:00', '%w') +1 AS weekVal UNION ALL
SELECT '周五' AS weekInfo, date_format( '2022-04-15 08:00:00', '%w') +1 AS weekVal UNION ALL
SELECT '周六' AS weekInfo, date_format( '2022-04-16 08:00:00', '%w') +1 AS weekVal UNION ALL
SELECT '周日' AS weekInfo, date_format( '2022-04-17 08:00:00', '%w') +1 AS weekVal

Greenplum 的函数获取的结果是字符，需要将结果转换成数值再-1即可实现与 MySQL 数据库函数结果一致。

-- Greenplum数据库
SELECT '周一' AS weekInfo, cast( to_char( to_timestamp( '2022-04-11 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal UNION ALL
SELECT '周二' AS weekInfo, cast( to_char( to_timestamp( '2022-04-12 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal UNION ALL
SELECT '周三' AS weekInfo, cast( to_char( to_timestamp( '2022-04-13 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal UNION ALL
SELECT '周四' AS weekInfo, cast( to_char( to_timestamp( '2022-04-14 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal UNION ALL
SELECT '周五' AS weekInfo, cast( to_char( to_timestamp( '2022-04-15 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal UNION ALL
SELECT '周六' AS weekInfo, cast( to_char( to_timestamp( '2022-04-16 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal UNION ALL
SELECT '周日' AS weekInfo, cast( to_char( to_timestamp( '2022-04-17 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS int) -1 AS weekVal

两种方式选择一种即可。

本文来源：https://blog.csdn.net/weixin_39168541/article/details/124120419

相关文章