时间获取及格式转换

---

一、mysql

获取当前年月：

mysql> SELECT CURDATE();   
        -> 2023-05-10
mysql> SELECT CURTIME();   
        -> 10:07:18

增加日期或时间：

mysql> SELECT ADDDATE('2023-05-10', INTERVAL 31 DAY);
mysql> SELECT ADDDATE('2023-05-10', 31);
mysql> SELECT CURTIME(),ADDTIME(CURTIME(),'1:1:1');

mysql> SELECT CURDATE(),ADDDATE(CURDATE(), INTERVAL 31 DAY);
+---------+------------------------------------------+
| CURDATE()| ADDDATE(curdate(), INTERVAL 31 DAY)     |
+----------------------------------------------------+
| 2023-05-10| 2023-06-10                             |
+---------+------------------------------------------+

获取当前时间:

sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。now()与timestamp同意

mysql> SELECT SLEEP(3),NOW(),SYSDATE(),CURRENT_TIMESTAMP(); 
+---------+---------------------+---------------------+---------------------+
| SLEEP(3)| NOW()               | SYSDATE()           |CURRENT_TIMESTAMP()  |
+-------------------------------+---------------------+---------------------+
| 0       | 2023-05-10 10:10:21 | 2023-05-10 10:10:24 |2023-05-10 10:10:21  |
+---------+---------------------+---------------------+---------------------+

date增加日期或时间：

mysql> SELECT NOW(),DATE(NOW());  #当前时间截取 日期部分  
        -> 2023-05-10 13:47:05,2023-05-10
mysql> SELECT DATEDIFF('2007-11-30 23:59:59','2007-12-30');  #计算两个时间差
        -> -31
mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);  #日期增加天或月...
        -> '2018-05-02'
mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);  #日期减少天或月...
        -> '2017-05-01'

日期转字符串格式

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'

日期转换为时间戳

mysql>SELECT UNIX_TIMESTAMP(CURTIME())  AS DATE;
        -> 1683699102
mysql>SELECT UNIX_TIMESTAMP('2023-05-10 12:23:00')  AS DATE;
        -> 1683692580

时间戳转换为日期

mysql> SELECT FROM_UNIXTIME(1447430881);
        -> '2015-11-13 10:08:01'
mysql> SELECT FROM_UNIXTIME(1447430881000/1000);
        -> '2015-11-14 00:08:01.0000'
mysql> SELECT FROM_UNIXTIME(1447430881) + 0;
        -> 20151113100801
mysql> SELECT FROM_UNIXTIME(1447430881,'%Y %D %M %h:%i:%s %x');
        -> '2015 13th November 10:08:01 2015'

具体见官网：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

二、spark sql

获取当前时间

SELECT current_timestamp();
+--------------------+
| current_timestamp()|
+--------------------+
|2023-04-14 18:42:...|
+--------------------+

SELECT to_timestamp('2016-12-31', 'yyyy-MM-dd');
+------------------------------------+
|to_timestamp(2016-12-31, yyyy-MM-dd)|
+------------------------------------+
|                 2016-12-31 00:00:00|
+------------------------------------+

具体见官网：https://spark.apache.org/docs/latest/sql-ref-functions-builtin.html#date-and-timestamp-functions

三、flink sql

代码或sql语句

具体见官网：https://nightlies.apache.org/flink/flink-docs-release-1.17/zh/docs/dev/table/functions/systemfunctions/