Playing with dates in Python and MySQL

05 Dec 2014

Reading time ~1 minute

A common task is to manipulate date and time variables in Python, and get them to interact nicely with a database - I’ll describe the use with MySQL here.

Datetime or Timestamp

There is a popular thread on StackOverflow about whether to use a UNIX Timestamp or MySQL DateTime, and the choice depends on what you’re storing, and what the purpose is. Timestamps are often used for defining things that have a fixed point in time, for example tracking changes to database records. DateTime represents a date and time, and also contains timezone information, dependant on where the timezone of the server. The following is posted on the thread, originally from the MySQL documentation:

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

...

The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.

Dates in Python

To get the current time and date, use the time module and strftime() function:

import time    
now = time.strftime('%Y-%m-%d %H-%M-%S')
print "Now:", now

>>> Now: 2014-12-06 21-31-20

However, to get a variable defining some other time, where all/some of that datetime is known, use datetime and strftime() again:

import datetime
then = datetime.datetime(2009,12,13)
print "Then object:", then
then = then.strftime('%Y-%m-%d %H-%M-%S')
print "Then string:", then

>>> Then object: 2009-12-13 00:00:00
>>> Then string: 2009-12-13 00-00-00

Dates in MySQL

The date field in MySQL has the format ‘YYYY-MM-DD’, whilst the datetime field has the format ‘YYYY-MM-DD hh-mm-ss’.

Dates can be inserted into python from variables using the date or datetime as a string, for example:

#!/usr/bin/env python

import MySQLdb

name_string = 'some test string'
date_string = '2014-12-06 21:05:30'

try:
	con = MySQLdb.connect('hostname', 'username', 'userpass', \
		'tablename')
	cur = con.cursor()
	cur.execute("INSERT INTO foo (foo_name, foo_date) \
		VALUES (%s, %s)", (name_string, date_string))
	cur.commit()

Reserved names…

As always, watch out for reserved names, in both code and database: e.g. calling MySQL column ‘date’ leads to problems.