How to compare the 2 datetime values if they are in str/text format?

Question

Blame it on my bad design. I have the column datatype of datetime as text. And now, I need to compare (<) 2 datetime values if one is greater than the other. Getting the exception as "not all arguments converted during string formatting"

The values are in "YYYY-MM-DD HH:MM:SS" format

Possible duplicate of [sqlite-convert-string-to-date](https://stackoverflow.com/questions/4428795/sqlite-convert-string-to-date). — Mayank Porwal, Nov 21 '18 at 06:30
I am actually trying delete few rows if datetime is less than 6 weeks for instance. — Harsha, Nov 21 '18 at 06:30
You should be able to truncate your strings to only use "YYYY-MM-DD" - then a stringcompare of `"2018-06-08" < "2018-06-04"` should work. You would need to "calculate" your targett date as string as well... — Patrick Artner, Nov 21 '18 at 06:36

score 0 · Answer 1 · answered Nov 21 '18 at 06:43

A solution is to use pandas.to_datetime to get the Timestamp object from the string for which a comparison is much easier. Example:

import pandas as pd

date1 = pd.to_datetime("1991-01-20 05:00:00")
date2 = pd.to_datetime("2005-10-01 15:12:00")
print(date1 < date2) # True

score 0 · Answer 2 · answered Nov 21 '18 at 09:37

You can use time.mktime(parse(ts).timetuple()) where 'ts' is your timestamp string.

Ex:

>>> ts1 = datetime.datetime.now().strftime("%Y-%M-%d %I:%M:%S")
>>> ts2 = datetime.datetime.now().strftime("%Y-%M-%d %I:%M:%S")
>>>
>>> ts1
'2018-02-21 03:02:58'
>>> ts2
'2018-03-21 03:03:11'
>>>
>>> time.mktime(parse(ts1).timetuple())
1519162378.0
>>>
>>> time.mktime(parse(ts2).timetuple())
1521581591.0

How to compare the 2 datetime values if they are in str/text format?

2 Answers2