How to translate datetime string to sql format?

Question

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!

I hope you're not planning on formatting or catenating this result to a query string. — Ilja Everilä
– Ilja Everilä, Commented May 13, 2016 at 12:51
I'm planning to write it as a tuple's attribute, yes, and what? — Павел Иванов
– Павел Иванов, Commented May 13, 2016 at 12:53
possible duplicate of stackoverflow.com/questions/12281975/… — lesingerouge
– lesingerouge, Commented May 13, 2016 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ä
– Ilja Everilä, Commented May 13, 2016 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
– jfs, Commented May 14, 2016 at 4:24

Community · Accepted Answer · 2017-05-23 12:25:19Z

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

edited May 23, 2017 at 12:25

CommunityBot

11 silver badge

answered May 13, 2016 at 13:04

Moses Koledoye

78.8k8 gold badges139 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lesingerouge Over a year ago

in Python 2 you will have a problem with the %z. Check this out: stackoverflow.com/questions/12281975/…

Moses Koledoye Over a year ago

@lesingerouge Thanks for noting. I've added the link you provided for python 2.x support.

Community · Accepted Answer · 2017-05-23 12:00:21Z

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

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered May 13, 2016 at 12:43

lesingerouge

1,1687 silver badges14 bronze badges

1 Comment

Павел Иванов Over a year ago

>>> 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 "<stdin>", line 1, in <module> 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'

Collectives™ on Stack Overflow

How to translate datetime string to sql format?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related