Storing Dates in MySQL
11th July 2003
DevShed have a new article on Date Arithmetic With MySQL, which acts as a kind of missing manual for MySQL’s powerful date arithmetic functions. It reminded me of something I’ve been meaning to write about for some time: my thoughts on storing dates in a PHP application that uses a MySQL backend
MySQL comes with a full featured API for handling dates, and several column types for use with the date and time functions. Unfortuantely, none of the available column types map directly to PHP’s internal format for handling dates, the Unix timestamp (an integer value recording the seconds since midnight on January 1st, 1970). I have seen many PHP applications where people have used a MySQL integer field to store date information as a Unix timestamp. While this works, it is inadvisable as it prevents you from using any of MySQLs powerful built in date handling abilities.
Instead, you can use MySQL’s UNIX_TIMESTAMP() and FROM_UNIXTIME() functions to convert Unix timestamps to MySQL date types as part of your SQL queries. This allows you to use MySQL’s internal date manipulation features without having to manually convert MySQL dates to PHP timestamps in your PHP applications. I usually use DATETIME fields to store timestamps, but the conversion functions work for any of MySQL’s date storage types.
Here’s a sample select query:
SELECT entries.*, UNIX_TIMESTAMP(added) as unixtime FROM enries ...
Assuming added is a DATETIME column, this adds an additional field to each returned row called ’unixtime’, containing an integer that can be passed straight to PHP’s handy
date() function for formatting. Going the other way:
INSERT INTO entries SET title = 'The title', added = FROM_UNIXTIME(1057941242), ...
The second example is less useful, but at least demonstrates the function. Incidentally, when inserting things in to a MySQL database with the current time it’s generally a good idea to use MySQL’s
NOW() function to set the time, like this:
INSERT INTO entries SET title = 'The title', added = NOW(), ...
Doing this increases consistency as it means that should you ever have a setup with multiple web servers talking to a single database server the database server’s time will be used as the standard, rather than potentially introducing errors from differing clocks on the server machines.
More recent articles
- Datasette Enrichments: a new plugin framework for augmenting your data - 1st December 2023
- llamafile is the new best way to run a LLM on your own computer - 29th November 2023
- Prompt injection explained, November 2023 edition - 27th November 2023
- I'm on the Newsroom Robots podcast, with thoughts on the OpenAI board - 25th November 2023
- Weeknotes: DevDay, GitHub Universe, OpenAI chaos - 22nd November 2023
- Deciphering clues in a news article to understand how it was reported - 22nd November 2023
- Exploring GPTs: ChatGPT in a trench coat? - 15th November 2023
- Financial sustainability for open source projects at GitHub Universe - 10th November 2023
- ospeak: a CLI tool for speaking text in the terminal via OpenAI - 7th November 2023
- DALL-E 3, GPT4All, PMTiles, sqlite-migrate, datasette-edit-schema - 30th October 2023