Can't convert time strings from csv to datetime

Question

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.

If you changed the code and the error message is the same, you must be running the wrong code. — Peter Wood, Feb 15 '20 at 23:18
Or the error is still the same. I already swapped format and date string. — ktw, Feb 15 '20 at 23:43
Variable and function names should follow the `lower_case_with_underscores` style. You appear to be mixing two different naming conventions in your program. — AMC, Feb 16 '20 at 01:26

score 2 · Answer 1 · answered Feb 15 '20 at 23:02

2

You give the parameters in the wrong order. The date string needs to go first, then the format string.

answered Feb 15 '20 at 23:02

peer

4,171
8
42
73

Changing `begin_intervall = datetime.datetime.strptime("%d.%m.%Y %I:%M:%S", dateTimeBegin) end_intervall = datetime.datetime.strptime("%d.%m.%Y %I:%M:%S", dateTimeEnd)` to `begin_intervall = datetime.datetime.strptime(dateTimeBegin, '%d.%m.%Y %I:%M:%S') end_intervall = datetime.datetime.strptime(dateTimeEnd, '%d.%m.%Y %I:%M:%S') ` doesn't solve the issue. The Error message is the same. – ktw Feb 15 '20 at 23:10
1

That's strange... Can you make a minimal reproducible example? – peer Feb 15 '20 at 23:37

score 0 · Answer 2 · answered Feb 15 '20 at 23:13

As per the datetime docs

the functions is defined as datetime.strptime(date_string, format)

This means the first parameter will be Date string (02.04.2019 01:30:30) or the variable dateTimeBegin and Second will be the format String ('%d.%m.%Y %I:%M:%S').

score 0 · Accepted Answer · answered Feb 16 '20 at 00:22

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.

Thank you very much! Regrettably I have no control over the recording. It is the worst case: The data is recorded by scientific equipment and then provided as Excel, without the possibility to change format or anything else. — ktw, Feb 16 '20 at 00:37

Can't convert time strings from csv to datetime

3 Answers3