各种数据库日期函数的使用

#各种数据库日期函数的使用

场景:在日常开发中使用了各种数据库,经常容易弄混,整理各数据库日期函数处理的方式

一.mysql/txsql/tidb/oceanbase数据库
0.获取当前时间
select sysdate(); 
2024-10-24 20:43:19.0
select now(); 
2024-10-24 20:43:33.0
select current_date(); 
2024-10-24
select current_time(); 
20:44:07
select current_timestamp(); 
2024-10-24 20:44:20.0

1.日期加减
select date_add(current_date(), interval -1 day)
2024-10-24
select date_add(current_date(), interval -1 month)
2024-09-25

2.日期转字符串
select DATE_FORMAT(sysdate(),'%Y-%m-%d')
2024-10-25
select DATE_FORMAT(sysdate(),'%Y-%m-%d %T')
2024-10-25 09:24:13
select DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%S')
2024-10-25 09:27:39

3.字符串转日期
select str_to_date('2024-12-12 12:12:12','%Y-%m-%d %H:%i:%S') 
2024-12-12 12:12:12
4.时间戳
select unix_timestamp(); 
1729819878

二.oracle数据库
0.获取当前时间
select CURRENT_DATE FROM dual;
2024-10-24 20:45:40.000
select CURRENT_TIMESTAMP FROM dual;
2024-10-24 20:46:17.859
SELECT SYSDATE FROM dual;
2024-10-25 09:16:45.000

1.日期加减
SELECT TO_DATE(TO_CHAR(SYSDATE - 3,'YYYYMMDD'),'YYYYMMDD') FROM dual;
2024-10-22 00:00:00.000
SELECT ADD_MONTHS(SYSDATE,1)  FROM dual;
2024-11-25 09:36:53.000
SELECT SYSTIMESTAMP  FROM dual;
2024-10-25 09:40:43.431

2.日期转字符串
SELECT to_char(SYSDATE,'YYYYMMDD') FROM dual;
20241025
SELECT to_char(SYSDATE,'YYYY-MM-DD HH:mi:SS') FROM dual;
2024-10-25 09:38:29
SELECT to_char(SYSDATE,'YYYY-MM-DD HH24:mi:SS') FROM dual;
2024-10-25 09:38:47

3.字符串转日期
SELECT to_date('2024-10-25','YYYY-MM-DD') FROM dual;
2024-10-25 00:00:00.000

4.时间戳
SELECT (SYSDATE - to_date('1970-01-01 08','YYYY-MM-DD HH24'))* 86400 FROM dual;
1729820650.999999999999999999999999999997



三.gauss数据库
0.获取当前时间
select sysdate(); 
2024-10-25 09:46:17
select now(); 
2024-10-25 09:46:17
select current_date(); 
2024-10-25
select current_time(); 
09:46:37
select current_timestamp(); 
2024-10-25 09:46:43

1.日期加减
select date_add(SYSDATE, 1)
2024-10-26 09:48:42
SELECT SYSDATE + integer '7' AS RESULT;
2024-11-01 09:50:26
SELECT SYSDATE + INTERVAL '7 day' AS RESULT;
2024-11-01 09:51:36
SELECT SYSDATE + INTERVAL '7 hour' AS RESULT;
2024-10-25 16:51:42
SELECT SYSDATE + INTERVAL '1 MONTH' AS RESULT;
2024-11-25 09:52:00

2.日期转字符串
SELECT to_char(SYSDATE,'YYYYMMDD') FROM dual;
20241025
SELECT to_char(SYSDATE,'YYYY-MM-DD HH:mi:SS') FROM dual;
2024-10-25 09:38:29
SELECT to_char(SYSDATE,'YYYY-MM-DD HH24:mi:SS') FROM dual;
2024-10-25 09:38:47

3.字符串转日期
SELECT to_date('2024-10-25','YYYY-MM-DD') FROM dual;
2024-10-25 00:00:00.000

4.时间戳

四.hive数据仓库
0.获取当前时间
select current_date(); 
2024-10-25
select current_timestamp(); 
2024-10-25 15:46:03.703 

1.日期加减
select date_add(current_timestamp(), 1);
2024-10-26

2.日期转字符串
select DATE_FORMAT(current_timestamp(),'yyyyMMdd');
20241025
select DATE_FORMAT(current_timestamp(),'yyyy-MM-dd hh:mm:ss');
2024-10-25 03:49:49
select DATE_FORMAT(current_timestamp(),'yyyy-MM-dd HH:mm:ss');
2024-10-25 15:50:14

3.字符串转日期
select substring(current_timestamp(), 1, 10);
2024-10-25
select substring(current_timestamp(), 1, 19);
2024-10-25 15:51:16

4.时间戳
select unix_timestamp();
1729842461