使用场景
日期换算往往用于UTC时间+时区
MySQL
DateTime
- mysql.DATE_ADD() 作用同于 sql.DATEADD()
- 减去8小时的日期为
2019-06-12
SELECT create_time, DATE_ADD(create_time, INTERVAL -8 HOUR)
FROM anchor_training_log
WHERE date(DATE_ADD(create_time, INTERVAL -8 HOUR)) = "2019-06-12";
- W3School参考:MySQL DATE_ADD() 函数
TimeStamp
用法同上
存为BigData格式的时间戳(python.time.time())
- FROM_UNIXTIME(秒时间戳, 格式):转换timestamp为datetime
- FLOOR():向下取整
SELECT
ctime/1000, FLOOR(ctime/1000), FLOOR(ctime/1000 + 60*60*24),
FROM_UNIXTIME(FLOOR(ctime/1000),'%Y-%m-%d %H:%i:%s') as datetime_format,
Date(FROM_UNIXTIME(FLOOR(ctime/1000),'%Y-%m-%d %H:%i:%s')) as date_part
FROM add_user_recharge_log
WHERE
Date(FROM_UNIXTIME(FLOOR(ctime/1000 + 60*60*24),'%Y-%m-%d %H:%i:%s')) = "2019-06-05"
;
- 把datetime转换成timestamp
SELECT
now(),
unix_timestamp(now())
FROM add_user_recharge_log
limit 10
;
SQL Server
TimeStamp
-
DATEADD(hour,2, creat_time)
:增加两小时 -
DATE(creat_time)
:获取日期"2019-06-28"
-
Amazon RedShift示例
select
e_target_jid,
dateadd(hour,2,event_time)
from app300049.event_p2p_call
where date(dateadd(hour,2,event_time))='2019-06-28'
limit 10;
-
注意:通过
psycopg2
获取后,timestamp字段很可能变成datetime.datetime类型。 -
W3School参考:SQL Server DATEADD() 函数