UNIX TIMESTAMP 与 TIME 之间的转换

本文主要总结一下Hive/MySQL里常用的时间函数： FROM_UNIXTIME() //unixtime–>time UNIX_TIMESTAMP() //time–>unixtime

1.1 FROM_UNIXTIME()的用法： FROM_UNIXTIME(unix_timestamp)， FROM_UNIXTIME(unix_timestamp,format) ps: unix_timestamp可以是字段名,也可以直接是Unix 时间戳，format主要是将返回值格式化。 eg: mysql>SELECT FROM_UNIXTIME( 1249488000, ‘%Y%m%d’ ) ->20071120 mysql>SELECT FROM_UNIXTIME( 1249488000, ‘%Y年%m月%d’ ) ->2007年11月20 mysql> Select FROM_UNIXTIME(875996580); -> ‘1997-10-04 22:23:00’ mysql> Select FROM_UNIXTIME(875996580) + 0; -> 19971004222300

1.2 UNIXTIME()中列名的用法： FROM_UNIXTIME(ctime, ‘yyyyMMdd’) = ‘20160611’

eg: SELECT ctime AS pushtime, FROM_UNIXTIME(ctime, ‘yyyyMMdd’) as pushtime2 FROM DB WHERE FROM_UNIXTIME(ctime, ‘yyyyMMdd’) = ‘20160611’ ;

2.UNIX_TIMESTAMP的用法： UNIX_TIMESTAMP() UNIX_TIMESTAMP(date)

eg: mysql> select UNIX_TIMESTAMP(); -> 882226357 mysql> select UNIX_TIMESTAMP(‘1997-10-04 22:23:00’); -> 875996580 WHERE ctime >UNIX_TIMESTAMP(‘2016-06-01 00:00:00’) and ctime < UNIX_TIMESTAMP(‘2016-06-15 00:00:00’)

3.unixtime转化为当天的分钟：

def getminite(unixtime): unixtime=unixtime/1000 x = time.localtime(unixtime) a= time.strftime(‘%Y-%m-%d %H:%M:%S’,x) timeArray = time.strptime(a[0:10]+” 00:00:00”, “%Y-%m-%d %H:%M:%S”) b=time.mktime(timeArray) return (int(unixtime)-int(b))/60