问题描述
不是一个很好的标题,所以我很抱歉.
Not a very good title, so my apologies.
由于某种原因,(我不是这样做的人,我离题了),我们有一个表结构,其中日期的字段类型为varchar. (奇数).
For some reason, (I wasn't the person who did it, i digress) we have a table structure where the field type for a date is varchar. (odd).
我们有一些日期,例如:
We have some dates, such as:
1932-04-01 00:00:00 and 1929-07-04 00:00:00
我需要执行一个查询,将这些日期字符串转换为unix时间戳,但是,在mySQL中,如果转换的日期为1970年之前,它将返回0.
I need to do a query which will convert these date strings into a unix time stamp, however, in mySQL if you convert a date which is before 1970 it will return 0.
有什么想法吗?
非常感谢!
错误的日期格式.哎呀.
Wrong date format. ooops.
推荐答案
啊!我们找到了解决方案!
Aha! We've found a solution!
要执行此操作的SQL:
The SQL to do it:
SELECT DATEDIFF( STR_TO_DATE('04-07-1988','%d-%m-%Y'),FROM_UNIXTIME(0))*24*3600 -> 583977600
SELECT DATEDIFF( STR_TO_DATE('04-07-1968','%d-%m-%Y'),FROM_UNIXTIME(0))*24*3600 -> -47174400
这可能对将来的参考很有用.
This could be useful for future reference.
您可以在此处进行测试: http://www.onlineconversion.com/unix_time.htm
You can test it here: http://www.onlineconversion.com/unix_time.htm
这篇关于将1970年之前的日期字符串转换为MySQL中的时间戳的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!