【SQL编程】Greenplum 数据库通过 timestamp 类型字段值实现数据的（日期时段筛选+时间时段筛选）跨天时段及不跨天时段SQL详情

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

why

最近在开发数据中台项目，其他平台的部分业务要迁移到中台实现，其他平台使用的数据库是 MySQL、Oracle 而中台使用的是 Greenplum 数据库，各个数据库都有各自的函数和字段类型，以下 SQL 是 MySQL 数据库业务迁移至 GP 数据库的转换。

SQL

以下 SQL 使用了 :four: 个参数，日期筛选字段参数：startTime、endTime（格式 2021-10-11）和时段筛选字段参数：startDuration、endDuration（格式 11:50:20）。

MySQL原始SQL

原MySQL数据库SQL（去掉一部分与时段筛选无关的代码） 主要使用的函数有 STR_TO_DATE、SUBSTRING、DATE_ADD、date_format 而这些 GP 数据都没有

-- 不跨天
SELECT
	* 
FROM
	data_bill b 
WHERE
	STR_TO_DATE ( b.call_time, '%Y-%m-%d' ) >= STR_TO_DATE ( #{startTime} , '%Y-%m-%d' ) 
	AND STR_TO_DATE ( b.call_time, '%Y-%m-%d' ) <= STR_TO_DATE ( #{endTime} , '%Y-%m-%d' ) 
	AND STR_TO_DATE ( SUBSTRING ( b.call_time, 12 ), '%H:%i:%s' ) >= STR_TO_DATE ( #{startDuration}, '%H:%i:%s' ) 
	AND STR_TO_DATE ( SUBSTRING ( b.call_time, 12 ), '%H:%i:%s' ) <= STR_TO_DATE ( #{endDuration}, '%H:%i:%s' )

-- 跨天
SELECT
	* 
FROM
	data_bill b 
WHERE
	STR_TO_DATE ( b.call_time, '%Y-%m-%d' ) >= STR_TO_DATE ( #{startTime}, '%Y-%m-%d' ) 
	AND STR_TO_DATE ( b.call_time, '%Y-%m-%d' ) <= STR_TO_DATE ( #{endTime} , '%Y-%m-%d' ) 
	AND (
		(
			STR_TO_DATE ( SUBSTRING ( b.call_time, 12 ), '%H:%i:%s' ) >= STR_TO_DATE ( #{startDuration}, '%H:%i:%s' ) 
			AND STR_TO_DATE ( SUBSTRING ( b.call_time, 12 ), '%H:%i:%s' ) <= STR_TO_DATE ( '23:59:59', '%H:%i:%s' ) 
		) 
		OR (
			STR_TO_DATE (
				SUBSTRING ( date_format ( DATE_ADD ( STR_TO_DATE ( b.call_time, '%Y-%m-%d %H:%i:%s' ), INTERVAL 1 DAY ), '%Y-%m-%d %H:%i:%s' ), 12 ),
				'%H:%i:%s' 
			) >= STR_TO_DATE ( '00:00:00', '%H:%i:%s' ) 
			AND STR_TO_DATE (
				SUBSTRING ( date_format ( DATE_ADD ( STR_TO_DATE ( b.call_time, '%Y-%m-%d %H:%i:%s' ), INTERVAL 1 DAY ), '%Y-%m-%d %H:%i:%s' ), 12 ),
				'%H:%i:%s' 
			) <= STR_TO_DATE ( #{endDuration}, '%H:%i:%s' ) 
		) 
	)

Greenplum转换后SQL

GP 数据库转换后的 SQL 主要使用了 date_trunc、to_date、SUBSTRING、CAST 函数 SUBSTRING 函数虽然名称上跟 MySQL 一致 但是用法是不同的

-- 不跨天
SELECT
	* 
FROM
	data_bill 
WHERE
	date_trunc( 'DAY', call_time ) >= to_date(  #{startTime}, 'yyyy-mm-dd' ) 
	AND date_trunc( 'DAY', call_time ) <= to_date( #{endTime}, 'yyyy-mm-dd' ) 
	AND call_time >= CAST ( SUBSTRING ( '' || call_time FROM 0 FOR 12 ) || #{startDuration} AS TIMESTAMP ) 
	AND call_time <= CAST ( SUBSTRING ( '' || call_time FROM 0 FOR 12 ) || #{endDuration} AS TIMESTAMP ) 
	
-- 跨天
SELECT
	* 
FROM
	data_bill 
WHERE
	date_trunc( 'DAY', call_time ) >= to_date(  #{startTime}, 'yyyy-mm-dd' ) 
	AND date_trunc( 'DAY', call_time ) <= to_date( #{endTime}, 'yyyy-mm-dd' ) 
	AND (
		(
			call_time >= CAST ( SUBSTRING ( '' || call_time FROM 0 FOR 12 ) || #{startDuration} AS TIMESTAMP ) 
			AND call_time <= CAST ( SUBSTRING ( '' || call_time FROM 0 FOR 12 ) || '23:59:59' AS TIMESTAMP ) 
		) 
		OR (
			call_time >= CAST ( SUBSTRING ( '' || call_time FROM 0 FOR 12 ) || '00:00:00' AS TIMESTAMP ) 
			AND call_time <= CAST ( SUBSTRING ( '' || call_time FROM 0 FOR 12 ) || #{endDuration} AS TIMESTAMP ) 
		) 
	)