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.

Different Formats

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 INT type (Integer)
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 REAL type (Floating Point)
The ISO 8601 format is a string which represents the date and time in an international format: YYYY-MM-DD HH:MM:SS.SSS.

In SQLite format, you use the TEXT type.

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

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 YYYY-MM-DD.
A Time is a string in the format HH:MM. You can also include seconds, and milliseconds, using HH::MM::SS and HH:MM:SS.SSS.
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 T in the form YYYY-MM-DD HH:MM:SS, which is not strictly ISO8601.
A Time Zone is added using something like ±HH:MM at the end of the string. UTC, aka “Zulu” time can be written as +00:00 or as Z.

ISO 8601 has many other variations, but SQLite only supports the above.

Date Functions

There is one main function function to manipulate various date formats:

strftime(format,timestring,modifier)

There are four additional functions, but they are really just convenient versions of the strftime function:

Function	Equivalent strftime()
date(timestring,modifiers…)	strftime('%Y-%m-%d',timestring,modifiers…)
time(timestring,modifiers…)	strftime('%H:%M:%S',timestring,modifiers…)
datetime(timestring,modifiers…)	strftime('%Y-%m-%d %H:%M:%S', timestring,modifiers…)
julianday(timestring,modifiers…)	strftime('%J',timestring,modifiers…)

One additional function would have been handy: a short way of converting to Unix Epoch.

`timestring`

The timestring can be one of the following formats

ISO 8601 Date: YYYY-MM-DD
ISO 8601 Time: HH:MM[SS[.SSS]]
Date and Time with either a space or T between: YYYY-MM-DD[T]HH:MM[SS[.SSS]]
now for the current datetime
Up to 10 digits for the Julian Day: D…

Converting Formats

The whole thing depends on converting from one form to another.

The short date(), time(), datetime() and 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:

Conversion	Formula
String → Epoch	strftime('%s',string)
Epoch → String	strftime('%Y-%m-%d',int,'unixepoch') date(int,'unixepoch')
String → Julian	strftime('%J',string) julianday(string)
Julian → String	strftime('%Y-%m-%d',real) date(real)
Epoch → Julian	strftime('%J',int,'unixepoch') julianday(int,'unixepoch')
Julian → Epoch	strftime('%s',real)

Where the date() function is used, you could also have used the time() or datetime() functions, or the corresponding strftime format.

The 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

The following 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:

Modifier	Meaning
[±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)
unixepoch	interpret `dddddddddd` as epoch seconds, not Julian date
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:

Unit	Code
Year	`%Y`: year (0000-9999)
Month	`%m`: month: 01-12
Day	`%d`: day of month(00) `%w`: day of week (0-6 with Sunday=0) `%j`: day of year (001-366)
Hour	`%H`: hour (00-24)
Minute	`%M`: minute (00-59)
Second	`%S`: seconds (00-59) `%f`: fractional seconds (SS.SSS)
Other	`%s`: seconds since 1970-01-01 (epoch) `%J`: Julian day number `%W`: week of year (00-53)

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).

Other Databases

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 strtotime():

$timestamp=strtotime($timestring);  //  -> epoch integer

Note that PHP tends to refer to the Epoch time as timestamp.

Conversely, if you have a PHP timestamp, you can convert it to a suitable string for the database using either date() or strftime():

$timestring=date($format,$timestamp);
$timestring=strftime($format,$timestamp);

SQLite

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
    …
);

PHP

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 strtotime() function

$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 date() or strftime()

//  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 strtotime() function:

strtotime($string,$timestamp)

Using Strings

In SQL, define the date as a string:

CREATE TABLE … (
    …,
    date TEXT,  --  Use Date String
    …
);

PHP

When writing a date to the table

If the time is a PHP timestamp, you will need to convert to a string using either date() or strftime()

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 strtotime()
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: date($format,strtotime($datestring))

//  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