Seconds ignored when fetching MySQL datetime field in Python

Question

I'm stuck in what seems to be a very silly problem: I'm using both mysql.connector and MySQLdb to retrieve some datetime data, when I fetchall() values to put them on an array, I need to parse each row, but when the datetime has any "double zeros" as seconds, that part of the datetime is ignored and my parsing fails. Here's the scenario:

mycursor.execute("select arrival_time from queue_element where status='Pending'")
arrival = mycursor.fetchall()

let's say it returns two rows:

>> arrival
>> [(datetime.datetime(2019, 10, 7, 2, 45, 48),), (datetime.datetime(2019, 10, 7, 2, 46),)]
In the database:

2019-10-07 02:45:48 
2019-10-07 02:46:00

So when I try to parse it with datetime.datetime, the rows that only posseses 5 values, with the seconds being ignored instead of displayed as 00, fails and my code breaks. Here's how I'm trying to achieve this:

for item in arrival:
    arrival_parsed = str(item).replace("(datetime.datetime(", "") # extracts only the numbers
    arrival_parsed = str(arrival_parsed).replace("),)", "") # extracts only the numbers
    arrival_parsed = str(datetime.strptime(arrival_parsed,'%Y, %m, %d, %H, %M, %S')) # works fine if seconds != 00, breaks if equal
The error:

ValueError: time data '2019, 10, 7, 2, 46' does not match format '%Y, %m, %d, %H, %M, %S'

Tried to play with datetime but no luck. Any hints on how to parse it correctly or retrieve the value with the zeros?

Thanks!

When you apply str() to a datetime instance, it returns only the "numbers" so replace() method does not make sense. — cpinamtz
– cpinamtz, Commented Oct 10, 2019 at 14:25

Daniel Ocando · Accepted Answer · 2019-10-10 14:30:11Z

2

I think a simple if-else condition can do the trick. Try with the following code:

from datetime import datetime
arrival = [(datetime(2019, 10, 7, 2, 45, 48),), (datetime(2019, 10, 7, 2, 46),)]

for item in arrival:
  if item[0].second != 0:
    arrival_parsed = str(item).replace("(datetime.datetime(", "") # extracts only the numbers
    arrival_parsed = str(arrival_parsed).replace("),)", "") # extracts only the numbers
    arrival_parsed = str(datetime.strptime(arrival_parsed,'%Y, %m, %d, %H, %M, %S'))
    print(arrival_parsed)
  else:
    arrival_parsed = str(item).replace("(datetime.datetime(", "") # extracts only the numbers
    arrival_parsed = str(arrival_parsed).replace("),)", "") # extracts only the numbers
    arrival_parsed =  arrival_parsed + ", 0"
    arrival_parsed = str(datetime.strptime(arrival_parsed,'%Y, %m, %d, %H, %M, %S'))
    print(arrival_parsed)

answered Oct 10, 2019 at 14:30

Daniel Ocando

3,8342 gold badges14 silver badges19 bronze badges

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

1 Comment

Jhonatan Cruz Over a year ago

I thought about making a if statement but couldn't get to the last element as you did with item[0].second. Works like a charm, thank you!

Collectives™ on Stack Overflow

Seconds ignored when fetching MySQL datetime field in Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related