Jython DATETIME Tip

Archived Topics
1

This tip can help you to determine the amount of time that has lapsed between two DATETIME values in MySQL.

I had a situation where I needed to determine the amount of time that had lapsed between (2) DATETIME values in MySQL. If you have run queries in Python you’ll know that a DATETIME value is returned as a string. So my first step was to find a way to convert the string to some type of Time or Date object in Python. I found that Python has a module called Time which has a function called strptime() which takes a string and converts it to a Time object. Just what I was looking for, only problem is that strptime() isn’t available in Jython 2.1, bummer. Then I found mktime() and it is available in Jython 2.1, problem here is that it converts a tuple into a Time object which meant I would have to parse the string value into a tuple which is more work than I wanted to do. I then looked to MySQL to try and solve my problem and that’s when I found UNIX_TIMESTAMP(). UNIX_TIMESTAMP() returns the number of seconds since 1970-01-01 00:00:00 as an integer. Using UNIX_TIMESTAMP() I could take the seconds of value1 and subtract the seconds of value2 and this would give me the amount of seconds that has lapsed between the two values. Here’s an example SQL query –

SELECT UNIX_TIMESTAMP(value_1) AS value_1, UNIX_TIMESTAMP(value_2) AS value_2 FROM table_name

I used the AS because the column names will be UNIX_TIMESTAMP(value_1) and UNIX_TIMESTAMP(value_2) if you don’t rename them.

I also found an unexpected benefit in using the UNIX_TIMESTAMP() function. If you have a NULL DATETIME value you will get the string value ‘Wed Dec 31 1969 18:00:00 CST’ in Python, I think this is because of the Java driver. But if the UNIX_TIMESTAMP() function encounters a NULL value you will get None in Python which is Python’s equivalent to NULL.

There are other Date and Time functions in MySQL that might come in handy if you need to do any kind of date or time manipulations in Python.

P.S. - Anything in the works to upgrade to Jython 2.2?

2

Pat,
MySQL has a timediff function that will return the difference of two datetimes. You could also subtract the UNIX_TIMESTAMP() values in the query. Is there some reason you’re doing the legwork with Jython instead of the database?

MySQL datetime functions listed here.

You’re right, Jython 2.2 is available as of August 22nd. I put in a feature request for the upgrade.

3

Judging by the return value of the TIMEDIFF() function I would guess that Python would receive this value as a string, which would put me in the same boat I was in before (trying to convert a string into a Time or Date object in Python).

I could subtract the values in the query but I am comparing different values based on another value and I prefer to perform logic in Python as apposed to in a query.

4

TIMESTAMPDIFF is much better than TIMEDIFF - MySQL has wonderful time manipulation functions. My advice is to use the tool most suited for the job - in this case MySQL vs Jython 2.1.

Jython 2.2 integration is a possibility, but would have to be carefully tested for backwards compatability.

Expert tip: all of java.util is available for use as well, including java.util.Date and java.util.Calendar. These are very capable libraries. Still, using MySQL’s functions is going to be easier.

One last note - the 1969 date you are seeing is due to the behavior of the MySQL JDBC connector. it treats null dates as “zero” dates, which is the millisecond before 1970 (a.k.a. “The Epoch”). There is a switch on the connector to have it treat nulls as nulls (like one would expect), but it seems to have stopped working with the latest versions of MySQL…

Hope this helps,