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
- Slop is the new name for unwanted AI-generated content - 8th May 2024
- Weeknotes: more datasette-secrets, plus a mystery video project - 7th May 2024
- Weeknotes: Llama 3, AI for Data Journalism, llm-evals and datasette-secrets - 23rd April 2024
- Options for accessing Llama 3 from the terminal using LLM - 22nd April 2024
- AI for Data Journalism: demonstrating what we can do with this stuff right now - 17th April 2024
- Three major LLM releases in 24 hours (plus weeknotes) - 10th April 2024
- Building files-to-prompt entirely using Claude 3 Opus - 8th April 2024
- Running OCR against PDFs and images directly in your browser - 30th March 2024
- llm cmd undo last git commit - a new plugin for LLM - 26th March 2024
- Building and testing C extensions for SQLite with ChatGPT Code Interpreter - 23rd March 2024