Search code examples
pythondatetimepython-datetime

Can't convert time strings from csv to datetime

I have a csv file with 2 timestamps per row consisting of date, time and a value:

02.04.2019 00:30:30;02.04.2019 00:36:05;6354
02.04.2019 02:36:05;02.04.2019 03:41:40;3453
02.04.2019 03:41:40;02.04.2019 04:47:15;5456

When I try to convert date and time string to datetime:

with open("file.csv", "rt", encoding='utf-8-sig') as source:
    reader = csv.reader(source)
    for row in csv.reader(source, delimiter=";"):
        dateTimeBegin = row[0]
        dateTimeEnd = row[1]
        begin_intervall = datetime.datetime.strptime("%d.%m.%Y %I:%M:%S", dateTimeBegin)
        end_intervall = datetime.datetime.strptime("%d.%m.%Y %I:%M:%S", dateTimeEnd)

I got:

Traceback (most recent call last):
  File ".\compare.py", line 48, in <module>
    begin_intervall = datetime.datetime.strptime("%d.%m.%Y %I:%M:%S", dateTimeBegin)
  File "C:\Users\foobar\AppData\Local\Programs\Python\Python38-32\lib\_strptime.py", line 568, in _strptime_datetime
    tt, fraction, gmtoff_fraction = _strptime(data_string, format)
  File "C:\Users\foobar\AppData\Local\Programs\Python\Python38-32\lib\_strptime.py", line 349, in _strptime
    raise ValueError("time data %r does not match format %r" %
ValueError: time data '%d.%m.%Y %I:%M:%S' does not match format '02.04.2019 00:30:30'

Why is that? When I try something like 

date_time_str ="08.04.2018 05:35:38"     
date_time_obj = datetime.datetime.strptime(date_time_str, '%d.%m.%Y %I:%M:%S')

it works fine.

EDIT: I swapped format and time string and used some other data:

02.04.2019 01:30:30;02.04.2019 02:36:05;6354 #works
02.04.2019 02:36:05;02.04.2019 03:41:40;3453 #works
02.04.2019 03:41:40;02.04.2019 04:47:15;5456 #works
03.04.2019 00:25:03;03.04.2019 01:30:12;5997 #ERROR
03.04.2019 01:30:12;03.04.2019 02:35:21;5993
03.04.2019 02:35:21;03.04.2019 03:40:30;5994

Error is still:

Traceback (most recent call last):
  File ".\new.py", line 12, in <module>
    begin_intervall = datetime.datetime.strptime(dateTimeBegin, "%d.%m.%Y %I:%M:%S")
  File "C:\Users\foobar\AppData\Local\Programs\Python\Python38-32\lib\_strptime.py", line 568, in _strptime_datetime
    tt, fraction, gmtoff_fraction = _strptime(data_string, format)
  File "C:\Users\foobar\AppData\Local\Programs\Python\Python38-32\lib\_strptime.py", line 349, in _strptime
    raise ValueError("time data %r does not match format %r" %
ValueError: time data '03.04.2019 00:25:03' does not match format '%d.%m.%Y %I:%M:%S'

Seems to be the 00:25:03. But %I is the 24hour format.

Solution

  • I think the reason the reason this works for an example like 02.04.2019 01:30:30 but not 03.04.2019 00:25:03 is because the hour number should be between 0 and 23 for %H and 1 to 12 for %I, you can find a table of this here. This is because %H represents an hour in 24-hour format while %I represents the hour in 12-hour format.

    To fix this, you should set your format string to '%d.%m.%Y %H:%M:%S' instead of '%d.%m.%Y %I:%M:%S'.

    By the way, if you're the one recording these timestamps and therefore have control over how they are recorded, I recommend using Unix Timestamps instead. This is because they are much easier to convert back and forth and leave no ambiguity about the time zone of these timestamps. If you decide to do this however, be sure to localize your datetime objects before you actually save them to the CSV file as unix timestamps. This question has a lot of wonderful examples of how you can do this.