DateTime String via MySQL DATE_FORMAT or DateTime.strftime

Question

I have a function that consumes a datetime string that is returned from a DB query. Right now the query returns a datetime object.

What I am looking for is what would be the preferred way to create my datetime string. I have not done any performance profiling yet, just looking for previous experiences from people.

score 1 · Answer 1 · answered Jun 24 '19 at 20:53

It depends.

Normally, the database is just a repository for data; it is not a formatting engine. This implies that you should expect to get strings like "2019-06-24 13:47:24" or numbers like 1561409293 and you deal with them from there.

However, it is often more straightforward to simply call DATE_FORMAT() in your SELECT statement. This is especially handy when the SELECT can generate the entire 'report' without further manipulation.

Another way to decide... Which approach requires fewer keystrokes on your part? Or has the least chance of programming errors? Or...

You say "consumes a datetime string that is returned from a DB query" -- but what will it do with it? If it will be manipulating it in more than one way, then a client "object" sounds like the better approach. If you will simply display the datetime, then DATE_FORMAT() may be better.

There is no noticeable performance difference.

This is what my line of thinking was. The query is pulling the last data value from the DB to feed into an API endpoint for updating data from that point. And the API requires it in an ISO string. By using the MySQL method I do not have to have another import. Trivial, but trying to keep things lean as I go. — Brian M., Jun 24 '19 at 21:24

score 0 · Answer 2 · answered Jun 24 '19 at 18:30

If you have a datetime object, could could just keep it around in your code as a datetime object, extracting whatever information you need from it. Then when you really need the actual string, use strftime to format it in the way you want.

>>> from datetime import datetime
>>> t = datetime.now()
>>> t
datetime.datetime(2019, 6, 24, 14, 23, 45, 835379)
>>> print(t.month)
6
>>> print(t.second)
45
>>> as_string = t.strftime("%B %d, %Y")
>>> print(as_string)
June 24, 2019
>>> as_another_string = t.strftime("%Y-%h-%d %H:%m")
>>> print(as_another_string)
2019-Jun-24 14:06

This page shows you the sorts of format codes you can call upon, in order to extract whichever date/time information you want to display in your string:

DateTime String via MySQL DATE_FORMAT or DateTime.strftime

2 Answers2