Working With SQLite Dates
SQLite is invisibly one of the most popular databases in the world, being as it is embedded inside other software. It is free, well designed, and allows an application to work with a database without the pain of actually setting up a database server.
SQLite is an under appreciated database for use with PHP. Often PHP developers reach for a full database such as PostgreSQL or MySQL/MariaDB when a much lighter solution would do. Since it doesn’t involve a separate server, and the data is all in a single file, it is also very easy to back up and to transfer to another web server.
However, SQLite does have a few quirks, which will befuddle many newcomers to SQLIte:
- Database columns do not have a type, but an affinity. This means that you can put any type of data in any column, but there is a default interpretation of the data.
- SQLite does not have a Date type (or affinity). Instead, you choose between integers, reals or strings, and convert your data accordingly.
It is the date which can cause a lot of heartache. Hopefully, the following will help.
One more thing: the distinction between date and time is somewhat artificial, similar to the distinction between metres and millimetres: one is a collection of the other. In the case of a date, it is a grouping of 1 day which is 86400 seconds.
In this discussion, the word date will be used to imply the date or time, for convenience.
You can find the official word on SQLite Dates & Times here: SQLite Query Language: Date And Time Functions
SQLite offers three ways of storing dates:
The Unix Epoch number of seconds since 1 January 1970. Earlier dates are stored as negative values.
In SQLite, you use the
The Julian Day is the number of days since the beginning of time, which is apparently Noon 24 November 4714 BC GMT, in the Proleptic (retrofitted) Gregorian calendar. Time is indicated as a decimal.
In SQLite you use the
REALtype (Floating Point)
The ISO 8601 format is a string which represents the date and time in an international format:
In SQLite format, you use the
The following sample table illustrates this:
DROP TABLE IF EXISTS dates; CREATE TABLE dates ( iso TEXT, julian REAL, epoch INT );
Of course, the whole trick is to use special SQLite functions to interpret the more generic types as dates.
The Unix Epoch
For reasons that are unlikely to become clear, Unix and its cousins measure dates and times as the number of seconds since 1 January 1970. This is referred to as the Unix Epoch, or Epoch for short.
In earlier 32 Bit systems, this resulted in a limited range ending in early 2038, after which time will go negative, and the world, as we know it, will end. Fortunately, 64 Bit systems extend that to a little under 300 billion years, by which time it is Somebody Else’s Problem.
Using Unix Epoch time means that the data will simply be an integer.
ISO 8601 is an international standard for representing dates and times as well as durations and intervals. It is based on the Gregorian calendar, which is the calendar that most of the world uses as the official calendar.
For our purposes, the most important parts of ISO 8610 are about dates and times.
- A Date is a string in the format
- A Time is a string in the format
HH:MM. You can also include seconds, and milliseconds, using
- A DateTime is at string which obviously combines a Date and a Time. It can be written in the form
YYYY-MM-DDTHH:MM:SS, with the time in any of the previous forms. SQLite also allows you to omit the
Tin the form
YYYY-MM-DD HH:MM:SS, which is not strictly ISO8601.
- A Time Zone is added using something like
±HH:MMat the end of the string. UTC, aka “Zulu” time can be written as
ISO 8601 has many other variations, but SQLite only supports the above.
There is one main function function to manipulate various date formats:
There are four additional functions, but they are really just convenient versions of the
|datetime(timestring,modifiers…)||strftime('%Y-%m-%d %H:%M:%S', timestring,modifiers…)|
One additional function would have been handy: a short way of converting to Unix Epoch.
timestring can be one of the following formats
- ISO 8601 Date:
- ISO 8601 Time:
- Date and Time with either a space or
nowfor the current datetime
- Up to 10 digits for the Julian Day:
The whole thing depends on converting from one form to another.
julianday() functions can be used to convert a time string into a suitable format. In some cases, it may be redundant if the time string is already in the right format.
Here is a brief summary of the conversions:
|String → Epoch||strftime('%s',string)|
|Epoch → String||strftime('%Y-%m-%d',int,'unixepoch')
|String → Julian||strftime('%J',string)
|Julian → String||strftime('%Y-%m-%d',real)
|Epoch → Julian||strftime('%J',int,'unixepoch')
|Julian → Epoch||strftime('%s',real)|
date() function is used, you could also have used the
datetime() functions, or the corresponding
datetime() function is not strictly in ISO 8601 format, since the date and time should be separated by a
T. If you need this, you will have to use the
strftime() format in full:
strftime('%Y-%m-%dT%H:%M:%S',…) -- ISO Date Time
SQLite does not include a short function for the Unix Epoch, which is unfortunate since, for example, PHP uses that format. Again we will need to use the
strftime() format in full:
strftime('%s',…) -- Unix Epoch
SELECT statement illustrates the main formats:
SELECT date('2013-02-15 09:20+06:00') AS isoDate, time('2013-02-15 09:20+06:00') AS isoTime, datetime('2013-02-15 09:20+06:00') AS dateTime, strftime('%Y-%m-%dT%H:%M:%S','2013-02-15 09:20+06:00') AS isoDateTime, julianday('2013-02-15 09:20+06:00') as julian, strftime('%s','2013-02-15 09:20+06:00') as epoch;
(The Chelyabinsk meteor entered Earth’s atmosphere over Russia on 15 February 2013, 9:20 am local time).
Working with Dates in SQLite
If you already have the appropriate format, you can enter it directly. However, if you don’t, you will need to push it through one of the functions above.
In any format, you can manipulate Dates and Times using the following:
Manipulating Dates & Times
The optional modifiers can be used to modify a date or time:
|[±n] days|hours|minutes|seconds|months|years||add interval|
|start of month|year|day||Move back (if necessary) to start of interval|
|weekday [n]||move forward, (if necessary) to weekday (0=Sunday)|
|utc|localtime||adjust datetime from one to the other|
You can also use multiple modifiers. For example:
SELECT date('now','1 month','2 days'); -- 1 month 2 days from now SELECT date('now','1 month','start of month'); -- start of next month SELECT date('now','1 month','-7 days', 'weekday 0'); -- Sunday
Formatting Dates and Times
As mentioned, the core function is
strftime(), which takes a format string as the first parameter. The different format codes are:
Working with PHP
Although you can use Julian dates, this is not so common in PHP applications. Generally:
- PHP works with dates and times as Unix Epoch values
- PHP tends to interchange dates and times as strings
That is, PHP has no native date type (most languages don’t).
Both PostgreSQL and MySQL/MariaDB have native date and type types. However, when PHP gets the data, it comes through as strings.
To convert a date/time string from a database to PHP, we use
$timestamp=strtotime($timestring); // -> epoch integer
Note that PHP tends to refer to the Epoch time as
As mentioned before, the Julian Date is not so common in this context. If you need to work with them, PHP has
gregoriantojd() and related functions.
That leaves us with a choice:
- Store date/times as an Epoch integer, which makes PHP happy
- Store date/times as as string, which gives us a behaviour similar to other databases
Using Unix Epoch
In SQL, define the date as an integer:
CREATE TABLE … ( …, date INT, -- Use Unix Epoch … );
When writing a date to the table
- If the time is already an Epoch integer, nothing else needs to be done
- If the time is a formatted string, first convert it using the PHP
$nowstamp = time(); // Epoch Integer $thenstamp = '2019-04-15'; // Date String $thenstamp = strtotime($thenstamp); // Converted to Epoch // Now add to the table
When reading a date from the table
- If you plan to do more manipulation in PHP, nothing else needs to be done at this point
- If you are ready for display, convert it using
// Data has come from the table as $timestamp $nextweek = strtotime('+1 week',$timestamp); // Add 1 week $date = date('Y-m-d',$timestamp); // Display in ISO 8601 format
Note that most actual date and time manipulation can be done with the
In SQL, define the date as a string:
CREATE TABLE … ( …, date TEXT, -- Use Date String … );
When writing a date to the table
If the time is a PHP timestamp, you will need to convert to a string using either
For the most part, either function will do.
date()is more common, while
strftime()is more flexible. They use completely different formatting codes.
If the time is a formatted string, it should be OK to use it …
… however, sometimes the candidate string is not suitably formatted, so you may need to reformat it.
$nowstring = date('Y-m-d',time()); // ISO 8601 date string $thenstring = '2019-04-15'; // Date String // Now add to the table
When reading a date from the table
- If you plan to do more manipulation in PHP, you will need to convert it to a timestring using
If you are ready for display, it may be ready to go …
… but probably not. Chances are you will want to reformat the string, so you will need to convert it to a timestring and back to a string:
// Data has come from the table as $datestring $nextweek = strtotime('+1 week',strtotime($datestring)); // Add 1 week $nextweek = date('j M Y', $nextweek); // now format it nicely $date = date('j M Y',strtotime($datestring)); // Display in Friendly format