Convert string date with mixed format to epoch

Question

I have list of dates which has mixed format like:

01-01-13
01-12-13
1/19/2013
1/21/2013
1/21/2013
1/30/2013
02-01-13
02-02-13
02-12-13
2/13/2013
2/23/2013
...

I want to convert that list into list of epoch (to keep it in SQLite db on Android). So I've written a script (based on this) to convert it:

#!/usr/bin/python

import time

with open('date.txt') as f:
    mylist = f.read().splitlines() 
    for date_time in mylist:
        if "/" in date_time:
            pattern = '%d/%m/%Y'
        else:
            pattern = '%d-%m-%Y'
        epoch = int(time.mktime(time.strptime(date_time, pattern)))
        print epoch

But it fails on the first entry with:

Traceback (most recent call last):
  File "dateconv.py", line 11, in <module>
    epoch = int(time.mktime(time.strptime(date_time, pattern)))
  File "/usr/lib/python2.7/_strptime.py", line 467, in _strptime_time
    return _strptime(data_string, format)[0]
  File "/usr/lib/python2.7/_strptime.py", line 325, in _strptime
    (data_string, format))
ValueError: time data '01-01-13\r\n' does not match format '%d-%m-%Y'
fr@Romanowski:~/Downloads$ python dateconv.py 
Traceback (most recent call last):
  File "dateconv.py", line 12, in <module>
    epoch = int(time.mktime(time.strptime(date_time, pattern)))
  File "/usr/lib/python2.7/_strptime.py", line 467, in _strptime_time
    return _strptime(data_string, format)[0]
  File "/usr/lib/python2.7/_strptime.py", line 325, in _strptime
    (data_string, format))
ValueError: time data '01-01-13' does not match format '%d-%m-%Y'

EDIT1:

Thanks to ajsp's answer I've changed to:

#!/usr/bin/python

import time

with open('date.txt') as f:
    mylist = f.read().splitlines() 
    for date_time in mylist:
        if "/" in date_time:
            pattern = '%d/%m/%Y'
        else:
            pattern = '%d-%m-%y'
        epoch = int(time.mktime(time.strptime(date_time, pattern)))
        print epoch

But it still fails with:

1356994800
1356994800
1356994800
1356994800
1367359200
1372629600
1372629600
1372629600
1380578400
1385852400
Traceback (most recent call last):
  File "dateconv.py", line 12, in <module>
    epoch = int(time.mktime(time.strptime(date_time, pattern)))
  File "/usr/lib/python2.7/_strptime.py", line 467, in _strptime_time
    return _strptime(data_string, format)[0]
  File "/usr/lib/python2.7/_strptime.py", line 325, in _strptime
    (data_string, format))
ValueError: time data '1/19/2013' does not match format '%d/%m/%Y'

due to missing leading 0 as docs says Month as a zero-padded decimal number.

It does not work because you don't have the day-month-year orientation in the correct order. You can't have a 19th month as there are only 12 in a year. Copy and paste the code below, it works with the sample data you supplied. — ajsp, Oct 19 '15 at 10:44
try to limit your questions to a single issue (your first issue is %Y -> %y, your second issue %d/%m -> %m/%d). Though [@ajsp's answer](http://stackoverflow.com/a/33209275/4279) fixes both issues. — jfs, Oct 19 '15 at 20:40

ajsp · Answer 1 · 2015-10-19T10:42:10.310

2

Read the datetime documentation.

%Y matches "Year with century as a decimal number" e.g 2013.
%y matches "Year without century as a zero-padded decimal number" e.g 13

Try this, you should really put a bit more effort in!

import time
with open('date.txt') as f:
    mylist = f.read().splitlines() 
    for date_time in mylist:
        if "/" in date_time:
            pattern = '%m/%d/%Y'
            print "1", pattern
        else:
            print "2"
            pattern = '%m-%d-%y'
        epoch = int(time.mktime(time.strptime(date_time, pattern)))
        print epoch

Hope it helps.

edited Oct 19 '15 at 10:42

answered Oct 19 '15 at 07:54

ajsp

2,512
22
34

Thanks' it helps but the format in input file is so wrong, that it fails in different place now, see updated question. – Marian Paździoch Oct 19 '15 at 09:02

Jon Clements · Answer 2 · 2015-10-19T09:23:22.463

The easiest way is to use a 3rd party library called dateutil which is installable via pip/easy_install - this saves you writing all the "how to handle dates of different formats" logic yourself.

from dateutil.parser import parse

for line in f.read().splitlines():
    dt = parse(line)
    print(dt, int(dt.timestamp()))

Which gives you:

2013-01-01 00:00:00 1356998400
2013-01-12 00:00:00 1357948800
2013-01-19 00:00:00 1358553600
2013-01-21 00:00:00 1358726400
2013-01-21 00:00:00 1358726400
2013-01-30 00:00:00 1359504000
2013-02-01 00:00:00 1359676800
2013-02-02 00:00:00 1359763200
2013-02-12 00:00:00 1360627200
2013-02-13 00:00:00 1360713600
2013-02-23 00:00:00 1361577600

If you don't want to use a 3rd party library, then instead of using strptime, extract the components and use them as ints to datetime instead. This gives the same results as above:

from datetime import datetime
import re

for line in f.read().splitlines():
    m, d, y = map(int, re.split('[-/]', line))
    # use a more appropriate cut-off if needs be  
    # this assumes any two digit year is meant to be 2000
    if y < 100: 
        y += 2000
    dt = datetime(y, m, d)
    print(dt, int(dt.timestamp()))

Convert string date with mixed format to epoch

2 Answers2