在mysql datetime和python timestamp之间转换的正确方法是什么? [英] what is the proper way to convert between mysql datetime and python timestamp?

问题描述

根据 http://dev.mysql.com/doc/refman/5.0/en/datetime.html .我找到了一种将字符串值YYYY-MM-DD HH:MM:SS转换为时间戳int的方法.

according to http://dev.mysql.com/doc/refman/5.0/en/datetime.html. i got to find a way to convert the string value 'YYYY-MM-DD HH:MM:SS' to a timestamp int.

我在python的文档中查找.

i looked up in python's doc.

我尝试过:

print(time.strptime('2013-01-12 15:27:43', '%Y-%m-%d %H:%M:%S'))   

python给我这样的结果.

python give me a result like this.

time.struct_time(tm_year = 2013，tm_mon = 1，tm_mday = 12，tm_hour = 15，tm_min = 27，tm_sec = 43，tm_wday = 5，tm_yday = 12，tm_isdst = -1)

time.struct_time(tm_year=2013, tm_mon=1, tm_mday=12, tm_hour=15, tm_min=27, tm_sec=43, tm_wday=5, tm_yday=12, tm_isdst=-1)

我尝试过将时间戳转换为YYYY-MM-DD HH:MM:SS格式

i tried this to convert timestamp to YYYY-MM-DD HH:MM:SS format

print(time.strftime('%Y-%m-%d %H:%M:%S',time.time()))

python给我一个类型错误.

python give me a type error.

我只使用时间戳来计算时间和日期，我希望python中已经有了一种简单高效的方法，而不必创建临时数据.

i only use timestamp to calculate time and date, i hope there's already a way in python, simple and efficient , and don't have to create temp data.

根据答案，我写了两种方法.希望会有所帮助

according to the answer i write two methods. hope it would be helpful

import time

def convertTimestampToSQLDateTime(value):
    return time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(value))

def convertSQLDateTimeToTimestamp(value):
    return time.mktime(time.strptime(value, '%Y-%m-%d %H:%M:%S'))

推荐答案

如果我没有正确理解，很乐意对此进行更新，但是这里有一些示例可能会有所帮助.请注意，这使用的是datetime模块而不是time.

Happy to update this if I'm not properly understanding, but here are a few examples which may help. Note that this uses the datetime module instead of time.

>>> import datetime

这里我们设置了示例时间戳记ts和格式f:

Here we set up an example timestamp ts and a format f:

>>> ts = '2013-01-12 15:27:43'
>>> f = '%Y-%m-%d %H:%M:%S'

类似于上面的操作，我们使用strptime函数(来自datetime.datetime)基于格式参数将字符串转换为datetime对象:

Similar to what you did above, we use the strptime function (from datetime.datetime) to convert our string into a datetime object based on the formatting parameter:

>>> datetime.datetime.strptime(ts, f)
datetime.datetime(2013, 1, 12, 15, 27, 43)

现在相反-在这里我们使用datetime.datetime.now()来获取当前时间作为datetime对象:

Now in reverse - here we use datetime.datetime.now() to get the current time as a datetime object:

>>> now = datetime.datetime.now()
>>> now
datetime.datetime(2013, 1, 12, 0, 46, 54, 490219)

在datetime情况下，实际上是在datetime对象本身上调用strftime方法，并使用格式设置参数作为参数:

In the datetime case, the strftime method is actually called on the datetime object itself, with the formatting parameter as an argument:

>>> now.strftime(f)   
'2013-01-12 00:46:54'

在您的情况下，出现错误的原因是time.time()返回浮点数:

In your situation, the reason you were getting an error is because time.time() returns a float:

>>> time.time()
1357980846.290231

但是time.strftime需要一个time元组，类似于上面的内容.不必陷入时间的疯狂螺旋，像time.localtime()这样的函数将返回上述的time元组并按预期返回:

But time.strftime needs a time tuple, similar to what you had above. Without getting into the maddening spiral that is time, a function such as time.localtime() will return the aforementioned time tuple and will return as you expect:

>>> now = time.localtime()
>>> now
time.struct_time(tm_year=2013, tm_mon=1, tm_mday=12, tm_hour=0, tm_min=55, tm_sec=55, tm_wday=5, tm_yday=12, tm_isdst=0)
>>> f = '%Y-%m-%d %H:%M:%S'
>>> time.strftime(f, now)
'2013-01-12 00:55:55'

这篇关于在mysql datetime和python timestamp之间转换的正确方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！