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()
As always, watch out for reserved names, in both code and database: e.g. calling MySQL column ‘date’ leads to problems.