1. 概述
本文介紹 SparkSQL 中的日期函式文法。
本文同時提供新增計算欄公式寫法和 SparkSQL 文法寫法，使用者可自行選擇。
2. 時間戳
推薦使用FineDataLink 新增計算欄中的 DATETONUMBER-日期轉化為13位時間戳
也可以使用 SparkSQL 文法寫法：
| 文法 | 範例 |
|---|---|
| unix_timestamp()：獲取本地時區下的時間戳 | select unix_timestamp() |
| from_unixtime(bigint unixtime[, string format])：將時間的秒值轉換 成 format 格式 ，如 from_unixtime(1250111000,"yyyy-MM-dd") 得到 2009-03-12 | select from_unixtime(1557737000,"yyyy-MM-dd hh:mm:ss") |
| unix_timestamp(string date)：將格式為 yyyy-MM-dd HH:mm:ss 的時間字串轉換成時間戳， 如 unix_timestamp('2009-03-20 11:30:01') = 1237573801。 | select unix_timestamp('2019-05-13 04:43:20') |
| unix_timestamp(string date, string pattern)：將指定時間字串格式字串轉換成 Unix 時間 戳，如果格式不對傳回 0，如：unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400 | select unix_timestamp('2019/05/13 04:43:20',"yyyy/MM/dd hh:mm:ss" |
3. 時間字串
| 說明 | SparkSQL 文法 | 新增計算列中公式寫法 | 推薦使用方式 |
|---|---|---|---|
| 傳回日期時間字串的年月日部分 | to_date(string timestamp) 範例：to_date("1970-01- 01 00:00:00") = 1970-01-01 | 新增計算列中寫法如下： TODATE(LEFT(2023-06-13 00:01:27)) 時間欄位需要為正文類型格式。 TODATE(FORMAT(cdate,"yyyy-MM-dd")) 時間欄位需要為日期或者時間類型格式。 回傳值：2023-06-13 00:00:00 | 推薦使用 FineDataLink 新增計算列中的函式TODATE、LEFT-截取前幾個字串、FORMAT-格式轉換 |
| 傳回日期時間字串的年份部分 | year(string date) 範例：year("1970-01-01") = 1970
| YEAR("2000/1/1")
| 推薦使用 FineDataLink 新增計算欄中的 YEAR-傳回年份 |
| 傳回當前日期時間屬於哪個季度 | quarter(date/timestamp/string) 範例：quarter('2015-04-0 8') =2
| 暫不支援 | |
| 傳回日期中的月 | month(string date) 範例：month("1970-11-0 1") = 1 | MONTH("2000/1/1") | 推薦使用 FineDataLink 新增計算欄中的MONTH-日期中的月份 |
| 傳回日期中的日 | day(string date) 範例： day("1970-11-01") = 1 | DAY("2000/1/1") | 推薦使用 FineDataLink 新增計算欄中的DAY-日期中的日 |
| 傳回某一指定時間的小時數 | hour(string date) 範例： hour('2009-07-30
12:58:59') = 12 | HOUR("11:32:40") | 推薦使用 FineDataLink 新增計算欄中的 HOUR-小時數 |
| 傳回某一指定時間的分鐘數 | minute(string date) 範例：minute('2009-07-30 12:58:59') = 58 | MINUTE("15:36:25") | 推薦使用 FineDataLink 新增計算欄中的 MINUTE-分鐘數 |
| 傳回某一指定時間的秒數 | second(string date) 範例：second('2009-07-30 12:58:59') = 59 | SECOND("15:36:25") | 推薦使用 FineDataLink 新增計算欄中的 SECOND-秒數 |
| 傳回一個代表一年中的第幾週的數字 | weekofyear(string date) 範例：weekofyear("1970- 11-01") = 44 | WEEK("2010/1/1") | 推薦使用 FineDataLink 新增計算欄中的 WEEK-求週數 |
| 傳回兩個指定日期間的天數、月數或年數 | datediff(string enddate ,string startdate) 範例：datediff('2009-03-0 1', '2009-02-27') = 2 | DATEDIF("2001/2/28","2004/3/20","Y") | 推薦使用 FineDataLink 新增計算欄中的 DATEDIF-日期差 |
| 傳回指定日期後，指定年/月/日/時/分/秒後的日期 | 從開始時間 startdate 加上 days ：date_add(string startdate, int days) 從開始時間 startdate 減去 days：date_sub(string startdate, int days) | ADDTODATE("2023-01-01", "y", 1)=2024-01-01 | 推薦使用 FineDataLink 新增計算欄中的ADDTODATE-指定日期後指定DELTA時間的日期 |
3. 獲取當前時間
| 說明 | SparkSQL 文法 | 新增計算欄中公式寫法 | 推薦使用方式 |
|---|---|---|---|
| 傳回當前時間 | current_timestamp 範例：select current_timestamp | NOW() | 推薦使用 FineDataLink 新增計算欄中的NOW-當前時間 |
| 傳回當前時間日期 | current_date 範例：select current_date | TODATE(FORMAT(NOW(),"yyyy-MM-dd"))
| 推薦使用 FineDataLink 新增計算列中的TODATE(FORMAT(NOW(),"yyyy-MM-dd")) |
4. 增加月數
add_months(string start_date, int num_months)：傳回當前時間下再增加 num_months 個月的 日期。
範例：select add_months('2019-05-13',120)
推薦使用 FineDataLink 新增計算列中的公式 date(year(today()),MONTH(today())+num_months,day(today()))，詳情參見：常見日期公式
5. 獲取月末日期
last_day(string date)：傳回這個月的最後一天的日期，忽略時分秒部分（HH:mm:ss）。
範例：select last_day('2019-02-01')
推薦使用 FineDataLink 新增計算欄中的公式 DATE(YEAR(日期),MONTH(日期)+1,1-1)，詳情參見：常見日期公式
6. 獲取開始年或月
使用 SparkSQL 函式 trunc(string date, string format)：傳回時間的最開始年份或月份。
範例：select trunc("2016-06-26","MM"),trunc("2016-06-26","YY")
則可以看到日期 2016-06-26 的年初和月初時間，如下圖所示：
當然也可以使用 FineDataLink 新增計算欄中的公式：DATE(YEAR(NOW()),01,01)、DATE(YEAR(NOW()),MONTH(NOW()),01)
更多範例詳情參見：常見日期公式
7. 日期月份差
months_between(date1, date2)：傳回 date1 與 date2 之間相差的月份，如 date1>date2，則傳回正，如果 date1<date2,則傳回負，否則傳回 0.0。
範例：select months_between("2019-04-01", "2019-06-01")
8. 獲取指定格式日期
date_format(date/timestamp/string ts, string fmt)：按指定格式傳回時間 date。
範例：select date_format('2019-05-13','MM/dd')
推薦使用 FineDataLink 新增計算欄中的公式FORMAT-格式轉換，傳回指定格式的正文時間資料。
9. 下週幾日期
next_day(string start_date, string day_of_week)：傳回當前時間的星期幾所對應的日期。
範例：select next_day('2019-05-13', 'TU') 即傳回 2019-05-13 日以後星期二第一次出現的日期。 如下圖所示：
上一篇：SparkSQL文法概述
