【SQL编程】Greenplum 与 MySQL 数据库获取周几函数及函数结果保持一致的方法
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 ALLSELECT '周二' AS weekInfo, date_format( '2022-04-12 08:00:00', '%w') AS weekVal UNION ALLSELECT '周三' AS weekInfo, date_format( '2022-04-13 08:00:00', '%w') AS weekVal UNION ALLSELECT '周四' AS weekInfo, date_format( '2022-04-14 08:00:00', '%w') AS weekVal UNION ALLSELECT '周五' AS weekInfo, date_format( '2022-04-15 08:00:00', '%w') AS weekVal UNION ALLSELECT '周六' AS weekInfo, date_format( '2022-04-16 08:00:00', '%w') AS weekVal UNION ALLSELECT '周日' 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 ALLSELECT '周二' AS weekInfo, to_char( to_timestamp( '2022-04-12 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALLSELECT '周三' AS weekInfo, to_char( to_timestamp( '2022-04-13 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALLSELECT '周四' AS weekInfo, to_char( to_timestamp( '2022-04-14 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALLSELECT '周五' AS weekInfo, to_char( to_timestamp( '2022-04-15 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALLSELECT '周六' AS weekInfo, to_char( to_timestamp( '2022-04-16 08:00:00', 'YYYY-MM-DD hh24:mi:ss' ), 'd' ) AS weekVal UNION ALLSELECT '周日' 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 ALLSELECT '周二' AS weekInfo, date_format( '2022-04-12 08:00:00', '%w') +1 AS weekVal UNION ALLSELECT '周三' AS weekInfo, date_format( '2022-04-13 08:00:00', '%w') +1 AS weekVal UNION ALLSELECT '周四' AS weekInfo, date_format( '2022-04-14 08:00:00', '%w') +1 AS weekVal UNION ALLSELECT '周五' AS weekInfo, date_format( '2022-04-15 08:00:00', '%w') +1 AS weekVal UNION ALLSELECT '周六' AS weekInfo, date_format( '2022-04-16 08:00:00', '%w') +1 AS weekVal UNION ALLSELECT '周日' 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 ALLSELECT '周二' 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 ALLSELECT '周三' 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 ALLSELECT '周四' 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 ALLSELECT '周五' 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 ALLSELECT '周六' 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 ALLSELECT '周日' 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
两种方式选择一种即可。