#	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](https://www.sqlite.org/lang_datefunc.html)

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:

```sql
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:

```sql
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')<br>date(int,'unixepoch') |
| String → Julian | strftime('%J',string)<br>julianday(string)                    |
| Julian → String | strftime('%Y-%m-%d',real)<br>date(real)                       |
| Epoch → Julian  | strftime('%J',int,'unixepoch')<br>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:

```sql
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:

```sql
strftime('%s',…)				-- Unix Epoch
```

The following `SELECT` statement illustrates the main formats:

```sql
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:

```sql
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)<br>__`%w`__: day of week (0-6 with Sunday=0)<br>__`%j`__: day of year (001-366)  |
| Hour   | __`%H`__: hour (00-24)                                                                                      |
| Minute | __`%M`__: minute (00-59)                                                                                    |
| Second | __`%S`__: seconds (00-59)<br>__`%f`__: fractional seconds (SS.SSS)                                          |
| Other  | __`%s`__: seconds since 1970-01-01 (epoch)<br>__`%J`__: Julian day number<br>__`%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()`](https://www.php.net/manual/en/function.strtotime.php):

```php
$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()`](https://www.php.net/manual/en/function.date.php) or [`strftime()`](https://www.php.net/manual/en/function.strftime.php):

```php
$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()`](https://www.php.net/manual/en/function.gregoriantojd.php) 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:

```sql
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

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

```php
//	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:

```php
strtotime($string,$timestamp)
```

### Using Strings

In SQL, define the date as a string:

```sql
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.

```php
$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))`

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