what is the proper way to convert between mysql datetime and python timestamp?

Question

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.

i looked up in python's doc.

i tried:

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

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)

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 give me a type error.

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'))

Answer 1

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

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'

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)

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)

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'

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

>>> time.time()
1357980846.290231

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'

Answer 2

I'm only adding this class to potentially save the next guy a little time. If anyone finds this useful, upvote RocketDonkey's answer.

## dev on v3.7.6

from datetime import datetime
from time import mktime, time


class Time:
    '''\
*Convenience class for easy format conversion*\n
Accepts time() float, datetime object, or SQL datetime str.\n
If no time arg is provided, object is initialized with time().\n
id kwarg can be used to keep track of objects.\n
Access formats as instance.t, instance.dt, or instance.sql.\
    '''

    f = '%Y-%m-%d %H:%M:%S'

    def __init__(self, *arg, id=None) -> None:
        self.id = id
        if len(arg) == 0:
            self.t = time()
            self.dt = self._dt
            self.sql = self._sql
        else:
            arg = arg[0]
            if isinstance(arg, float) or arg == None:
                if isinstance(arg, float):
                    self.t = arg
                else:
                    self.t = time()
                self.dt = self._dt
                self.sql = self._sql
            elif isinstance(arg, datetime):
                self.t = arg.timestamp()
                self.dt = arg
                self.sql = self._sql
            elif isinstance(arg, str):
                self.sql = arg
                if '.' not in arg:
                    self.dt = datetime.strptime(self.sql, Time.f)
                else:
                    normal, fract = arg.split('.')
                    py_t = datetime.strptime(normal, Time.f)
                    self.dt = py_t.replace(
                        microsecond=int(fract.ljust(6, '0')[:6]))
                self.t = self.dt.timestamp()

    @property
    def _dt(self) -> datetime:
        return datetime.fromtimestamp(self.t)

    @property
    def _sql(self) -> str:
        t = self.dt
        std = t.strftime(Time.f)
        fract = f'.{str(round(t.microsecond, -3))[:3]}'
        return std + fract

    def __str__(self) -> str:
        if self.id == None:
            return self.sql
        else:
            return f'Time obj "{self.id}": {self.sql}'


def test():
    def test_one(*arg):
        t = Time(*arg, id=type(*arg))
        print(t)
        print(t.t)
        print(t.dt)

    sql = '2020-01-22 15:30:33.433'
    time_float = 1579927395.3708763
    dt_obj = datetime.now()
    for datum in [sql, time_float, dt_obj, None]:
        test_one(datum)


if __name__ == '__main__':
    test()