SQL经典案例之不同数据库的日期算术运算

原创锁钥 2024-12-22

336

SQL经典案例之不同数据库日期算术运算的小区别

构造测试数据
日期类型
加减日期时间

Oracle
MySQL
PostgreSQL

两个日期相差的天数
两个日期相差的月/年数

MONTHS_BETWEEN
YEAR/MONTH
EXTRACT

构造测试数据

-- postgresql/mysql
drop table t_date_time;
create table t_date_time(tid int,tdate date,ttime time,ttstamp timestamp);
insert into t_date_time values(1,current_timestamp,current_timestamp,current_timestamp);
insert into t_date_time values(2,'2025-01-01','09:00:00','2025-01-01 09:00:00');
insert into t_date_time values(3,'2025-01-28','13:14:56','2025-01-28 13:14:56');
insert into t_date_time values(4,'2025-02-04','04:03:01','2025-02-04 04:03:01');
insert into t_date_time values(5,'2025-04-04','14:04:50','2025-04-04 14:04:50');
insert into t_date_time values(6,'2025-04-05','15:05:50','2025-04-05 15:05:50');
insert into t_date_time values(7,'2025-04-06','16:04:50','2025-04-06 16:04:50');
insert into t_date_time values(8,'2025-05-31','13:31:33','2025-05-31 13:31:33');
insert into t_date_time values(9,'2025-06-02','06:06:06','2025-06-02 06:06:06');
insert into t_date_time values(10,'2025-10-01','10:10:10','2025-10-01 10:10:10');

-- oracle
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
alter session set nls_timestamp_format='YYYY-MM-DD HH24:MI:SS:FF6';
col ttstamp format a30
drop table t_date_time;
create table t_date_time(tid int,tdate date,ttstamp timestamp);
insert into t_date_time values(1,current_timestamp,current_timestamp);
insert into t_date_time values(2,'2025-01-01 09:00:00','2025-01-01 09:00:00');
insert into t_date_time values(3,'2025-01-28 13:14:56','2025-01-28 13:14:56');
insert into t_date_time values(4,'2025-02-04 04:03:01','2025-02-04 04:03:01');
insert into t_date_time values(5,'2025-04-04 14:04:50','2025-04-04 14:04:50');
insert into t_date_time values(6,'2025-04-05 15:05:50','2025-04-05 15:05:50');
insert into t_date_time values(7,'2025-04-06 16:04:50','2025-04-06 16:04:50');
insert into t_date_time values(8,'2025-05-31 13:31:33','2025-05-31 13:31:33');
insert into t_date_time values(9,'2025-06-02 06:06:06','2025-06-02 06:06:06');
insert into t_date_time values(10,'2025-10-01 10:10:10','2025-10-01 10:10:10');
insert into t_date_time values(11,sysdate,sysdate);
commit;

select * from t_date_time;

日期类型

pg和mysql的date类型都是年月日，oracle则是年月日时分秒
oracle的sysdate为是年月日时分秒，而current_timestamp则带小数秒和时区
pg 的timestamp类型默认不带时区，可以使用without/with time zone 来指定是否带时区

加减日期时间

Oracle

可用加减法运算来加减天数
年月可用add_months函数
对timestamp类型建议使用 INTERVAL 方式做运算，不然会丢失小数秒位的精度

select tid,ttstamp,
		ttstamp + interval '5' second as tsp_plus_5S,
		ttstamp + interval '5' minute as tsp_plus_5MI,
		ttstamp + interval '5' hour as tsp_plus_5H,
		ttstamp - 5 as tsp_minus_5D,
		add_months(ttstamp,-5) as tps_minus_5M,
		add_months(ttstamp,5*12) as tps_plus_5Y
from t_date_time where rownum<3;

MySQL

用关键字 INTERVAL 指定单位和数量
也可使用 DATE_ADD 函数

select tid,ttstamp,
		ttstamp - interval 5 second as tps_minus_5S,
		ttstamp - interval 5 minute as tps_minus_5MI,
		ttstamp - interval 5 hour as tps_minus_5H,
		ttstamp - interval 5 day as tsp_minus_5D,
		ttstamp - interval 5 month as tps_minus_5M,
		ttstamp + interval 5 year as tps_plus_5Y,
		date_add(ttstamp,interval -5 day) as tsp_add_minus_5D,
		date_add(ttstamp,interval -5 month) as tsp_add_minus_5M,
		date_add(ttstamp,interval 5 year) as tsp_add_plus_5Y
from t_date_time limit 2\G

PostgreSQL

用关键字 INTERVAL 指定单位和数量且需放在单引号内
注意秒和分钟的关键字结尾是带 s 的

select tid,ttstamp,
		ttstamp + interval '5 seconds' as tsp_plus_5S,
		ttstamp + interval '5 minutes' as tsp_plus_5MI,
		ttstamp + interval '5 hour' as tsp_plus_5H,
		ttstamp - interval '5 day' as tsp_minus_5D,
		ttstamp - interval '5 month' as tps_minus_5M,
		ttstamp + interval '5 year' as tps_plus_5Y
from t_date_time limit 2;

两个日期相差的天数

-- postgresql/oracle
select tsp_2,tsp_3,tsp_3 - tsp_2 from (
	select ttstamp as tsp_2 from t_date_time where tid = 2) x,
	(select ttstamp as tsp_3 from t_date_time where tid = 3) y;
-- mysql
select tsp_2,tsp_3,datediff(tsp_3,tsp_2) from (
	select ttstamp as tsp_2 from t_date_time where tid = 2) x,
	(select ttstamp as tsp_3 from t_date_time where tid = 3) y;

MySQL的DATEDIFF(end_date, start_date)函数，返回一个整数，可以为负数

end_date：表示要计算的时间段的结束日期
start_date：表示要计算的时间段的开始日期

两个日期相差的月/年数

找出两个日期相隔的月份，再除以12即相隔多少年

MONTHS_BETWEEN

Oracle使用months_between函数确定两个日期相隔的月数，日期相同返回 0

select tsp_2,tsp_9,months_between(tsp_9,tsp_2) as btmn,months_between(tsp_9,tsp_2)/12 as btyear
from (
	select ttstamp as tsp_2 from t_date_time where tid = 2) x,
	(select ttstamp as tsp_9 from t_date_time where tid = 9) y;

2025-06-02 06:00:00 与 2025-01-01 09:00:00 相差5个月加（24-9+6）小时，除以24转成天，再按月换算成小数(在oracle里面，以31天为基数)：

YEAR/MONTH

MySQL使用函数 YEAR 和 MONTH 返回指定日期的4位年份和2位月份

select tsp_2,tsp_9,year(tsp_9),year(tsp_2),month(tsp_9),month(tsp_2),
	(year(tsp_9) - year(tsp_2))*12 + (month(tsp_9) - month(tsp_2)) as btmn,
	(year(tsp_9) - year(tsp_2))*12 + (month(tsp_9) - month(tsp_2))/12 as btyear
from (
	select ttstamp as tsp_2 from t_date_time where tid = 2) x,
	(select ttstamp as tsp_9 from t_date_time where tid = 9) y;

EXTRACT

PostgreSQL使用 EXTRACT 函数返回指定日期的4位年份和2位月份，该函数用于从一个日期或时间型的字段内抽取年、月、日、时、分、秒数据，支持关健字 YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、WEEKDAY、YEARDAY

EXTRACT（关健字 FROM 日期或时间型字段）

select tsp_2,tsp_9,extract(year from tsp_9),extract(year from tsp_2),extract(month from tsp_9),extract(month from tsp_2),
	(extract(year from tsp_9) - extract(year from tsp_2))*12 + (extract(month from tsp_9) - extract(month from tsp_2)) as btmn,
	((extract(year from tsp_9) - extract(year from tsp_2))*12 + (extract(month from tsp_9) - extract(month from tsp_2)))/12 as btyear
from (
	select ttstamp as tsp_2 from t_date_time where tid = 2) x,
	(select ttstamp as tsp_9 from t_date_time where tid = 9) y;