2

Is there any shorter method to translate this ISO 8601 compatible UTC time to the SQL DATETIME format?

>>> str = "2016-03-28T20:23:46+0800"
>>> temp = str.split('T')
>>> temp[1] = temp[1].rstrip('+')
>>> temp[1]
'20:23:46+0800'
>>> temp[1] = temp[1].split('+')[0]
>>> result = " ".join(temp)
>>> result
'2016-03-28 20:23:46'

Thanks!

Павел Иванов
  • 1,863
  • 5
  • 28
  • 51
  • I hope you're not planning on formatting or catenating this result to a query string. – Ilja Everilä May 13 '16 at 12:51
  • I'm planning to write it as a tuple's attribute, yes, and what? – Павел Иванов May 13 '16 at 12:53
  • possible duplicate of http://stackoverflow.com/questions/12281975/convert-timestamps-with-offset-to-datetime-obj-using-strptime – lesingerouge May 13 '16 at 12:54
  • If by writing it as a tuple's attribute you mean insert it to a table, I mean that manually constructing query strings and passing values by formatting or concatenating them in to the query string is a very bad thing to do. I'm not saying you're doing so, but asking. DB-API's handle passing proper `datetime` objects as arguments to a placeholder query correctly. – Ilja Everilä May 13 '16 at 12:57
  • if you use Python bindings for your sql db then it should be able to convert a datetime object automatically i.e., all you need is to convert the string into `datetime` object. It is more likely that you won't introduce timezone-related errors in this case. – jfs May 14 '16 at 04:24

2 Answers2

5

You can simply switch formats:

>>> date_str = "2016-03-28T20:23:46+0800"
>>> format = '%Y-%m-%dT%H:%M:%S%z'
>>> new_format = '%Y-%m-%d %H:%M:%S' 
>>> datetime.datetime.strptime(date_str, format).strftime(new_format)
'2016-03-28 20:23:46'

This will not work in python 2.x as it does not support the %z flag. See timezone python 2 support for a workaround

Community
  • 1
  • 1
Moses Koledoye
  • 77,341
  • 8
  • 133
  • 139
  • 1
    in Python 2 you will have a problem with the %z. Check this out: http://stackoverflow.com/questions/12281975/convert-timestamps-with-offset-to-datetime-obj-using-strptime – lesingerouge May 13 '16 at 13:06
  • @lesingerouge Thanks for noting. I've added the link you provided for python 2.x support. – Moses Koledoye May 13 '16 at 13:13
1

There is no easy way to do this. Checkout this post for more details on possible solutions.

If you're looking for a quick hack try this:

st = '2016-03-28T20:23:46+0800'
st[:19].replace("T", " ")

Or, if you need the date in datetime:

datetime.datetime.strptime(st[:19], '%Y-%m-%dT%H:%M:%S')
Community
  • 1
  • 1
lesingerouge
  • 1,160
  • 7
  • 14
  • >>> str '2016-03-28T20:23:46+0800' >>> import datetime >>> datetime.datetime.strptime(str, "%Y-%m-%d %H:%M:%S").isoformat(" ") Traceback (most recent call last): File "", line 1, in File "/usr/lib/python2.7/_strptime.py", line 325, in _strptime (data_string, format)) ValueError: time data '2016-03-28T20:23:46+0800' does not match format '%Y-%m-%d %H:%M:%S' – Павел Иванов May 13 '16 at 12:46