Spark 時間計算算式:
1.TRACK_OUT_TIME - TRACK_IN_TIME 取秒差
(unix_timestamp( [TRACK_OUT_TIME] ) - unix_timestamp( [TRACK_IN_TIME] ))
2.TRACK_OUT_TIME - TRACK_IN_TIME 取分鐘差
(unix_timestamp( [TRACK_OUT_TIME] ) - unix_timestamp( [TRACK_IN_TIME] )) / 60
3.TRACK_OUT_TIME - TRACK_IN_TIME 取小時差
(unix_timestamp( [TRACK_OUT_TIME] ) - unix_timestamp( [TRACK_IN_TIME] )) / 60 / 60
4.TRACK_OUT_TIME - TRACK_IN_TIME 取天數差
(unix_timestamp( [TRACK_OUT_TIME] ) - unix_timestamp( [TRACK_IN_TIME] )) / 60 / 60 / 24
5.TRACK_OUT_TIME 加上1秒
cast(unix_timestamp(from_unixtime(unix_timestamp( [TRACK_OUT_TIME] ) + 1),‘yyyy-MM-dd HH:mm:ss’) as timestamp)
6.TRACK_OUT_TIME 加上1分鐘
cast(unix_timestamp(from_unixtime(unix_timestamp( [TRACK_OUT_TIME] ) + 60),‘yyyy-MM-dd HH:mm:ss’) as timestamp)
7.TRACK_OUT_TIME 加上1小時
cast(unix_timestamp(from_unixtime(unix_timestamp( [TRACK_OUT_TIME] ) + (60*60)),‘yyyy-MM-dd HH:mm:ss’) as timestamp)
8.TRACK_OUT_TIME 加上1天
cast(unix_timestamp(from_unixtime(unix_timestamp( [TRACK_OUT_TIME] ) + (606024)),‘yyyy-MM-dd HH:mm:ss’) as timestamp)
9.取目前系統時間
current_timestamp()
Spark 轉型算式:
1.欄位轉數值
case(xxx as double)
2. 欄位轉文字
case(xxx as String)
3.文字轉時間
to_timestamp(‘2019-09-20 14:00:00’)