SQLite Date and Time Functions – explained

A while back, I was greatly confused by SQLite date and time functions. It took me a while to figure out what was wrong. (It was my error: I hadn’t observed the rule that dates must have this form “YYYY-MM-DD” – four digit year, two-digit month and day.)

Nevertheless, I found that the documentation wasn’t quite clear, so I wrote up these notes as an adjunct to SQLite Datatypes and the SQLite Date and Time Functions pages.


2.2. Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times.
The conventional way to store dates is as a string in a TEXT field.
These fields can be compared directly (as strings) to determine equality or order.

For other date-as-string formats, see Date Strings on the Date And Time Functions page.

For further manipulations on dates and times, the built-in Date And Time Functions of SQLite convert dates and times between TEXT, REAL, or INTEGER values:

  • TEXT as strings (“YYYY-MM-DD HH:MM:SS.SSS” – with leading zero where required, and four-digit year – a so-called “timestring”)
  • REAL as Julian day numbers, the number of days (with fractional part) since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.


Date And Time Functions

The Date and Time Functions page doesn’t really define the the arguments or the return types, so I make them explicit below.

Timestring: The conventional way to store dates is as a timestring – a TEXT field (e.g., “YYYY-MM-DD HH:MM:SS.SSS”). These fields can be compared directly (as strings) to determine equality or order.

To convert to other date representations, SQLite supports five date and time functions. All take a timestring (a subset of IS0 8601 date and time formats, listed below) as an argument. The timestring is followed by zero or more modifiers. The strftime() function also takes a format string as its first argument.

  1. date(timestring, modifier, modifier, …) Returns the date as a string: “YYYY-MM-DD”.
  2. time(timestring, modifier, modifier, …) Returns the time as a string: “HH:MM:SS”.
  3. datetime(timestring, modifier, modifier, …) Returns a string: “YYYY-MM-DD HH:MM:SS”.
  4. julianday(timestring, modifier, modifier, …) Returns the Julian day as an REAL – the number of days (and fractional part) since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar).
  5. strftime(format, timestring, modifier, modifier, …) Returns the date formatted according to the format string specified as the first argument. The format string supports the most common substitutions found in the strftime() function from the standard C library plus two new substitutions, %f and %J.

… see the original SQLite page for modifiers and legal timestring formats …

Examples

This section replicates the examples of the original page, but includes the results and types of the function.

Compute the current date. Returns timestring.

SELECT date('now');  -- Result: 2018-03-07

Compute the last day of the current month. Returns timestring.

SELECT date('now','start of month','+1 month','-1 day'); -- Result: 2018-03-31

Compute the date and time given a unix timestamp 1092941466. Returns timestring.

SELECT datetime(1092941466, 'unixepoch'); -- Result: 2004-08-19 18:51:06

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone. Returns timestring.

SELECT datetime(1092941466, 'unixepoch', 'localtime'); -- Result: 2004-08-19 14:51:06

Compute the current unix timestamp. Returns INTEGER.

SELECT strftime('%s','now');  -- Result: 1520444198

Compute the number of days since the signing of the US Declaration of Independence. Returns REAL – days and fractions of a day.

SELECT julianday('now') - julianday('1776-07-04'); -- Result: 88269.7339379285

Compute the number of seconds since a particular moment in 2004: Returns INTEGER.

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56'); -- Result: 447519729

Compute the date of the first Tuesday in October for the current year. Returns timestring.

SELECT date('now','start of year','+9 months','weekday 2'); -- Result: 2018-10-02

Compute the time since the unix epoch in seconds (like strftime(‘%s’,’now’) except includes fractional part). Returns REAL – days and fractions of a day.

SELECT (julianday('now') - 2440587.5)*86400.0; -- Result: 1520444280.01899

A Practical Tutorial for SQLite Date Functions

The SQLite document doesn’t really show how to use date functions in actual code. Here is an example of inserting and retrieving dates in a table.

Note: It is good practice to store dates as text in a datestring format – YYYY-DD-MM. The “BMW” entry below is inserted as an integer number of seconds, and doesn’t work right when trying to use the julianday() function

bash-3.2$ sqlite3
SQLite version 3.29.0 2019-07-10 17:32:03
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table car_table(car_name text, car_date text);
sqlite> insert into car_table values ("Ford", date('now'));
sqlite> insert into car_table values ("Toyota", date('now','7 days'));
sqlite> insert into car_table values ("BMW", strftime('%s','now'));
sqlite> select * from car_table;
Ford|2019-12-15
Toyota|2019-12-22
BMW|1576431883       <-- uh oh, this is in seconds, not a datestring
sqlite> select car_name, julianday(car_date) from car_table;
Ford|2458832.5
Toyota|2458839.5
BMW|                 <-- uh oh, this isn't a julianday, as expected
sqlite>              ^D to exit

Local by Flywheel won’t start because it’s regenerating Docker Machine TLS certificates

I have been using Local by Flywheel and really enjoying it. It does two things:

  1. You can stand up a development version of a WordPress site on your laptop and horse around with it. It’s fast, you can make experiments, and if it blows up, you can simply regenerate in a minute or two.
  2. Using the (paid) Flywheel hosting, you can transfer your local dev server to their public hosting, and you’re on the air.

I have not used this latter facility, but I’m here to tell you that the first part is pretty slick.

But… I went away from Local by Flywheel for a month or so, then came back to start working on a new site. When I wanted to start it up, I saw a succession of messages stating that it was “Regenerating Machine Certificates” and that “Local detected invalid Docker Machine TLS certificate sand is fixing them now.” This looped apparently forever, and wouldn’t work. Here’s my report on their community forum.

After considerable searching, I found a procedure from one of the developers that seems to do the trick. It involves downloading a new version of the Boot2Docker ISO file, and letting the system re-provision itself. The process involved a) Creating an alias (“local-docker-machine”) for the “Local by Flywheel”s docker-machine binary; b) issuing a series of commands to that alias:

local-docker-machine stop local-by-flywheel
rm -rf ~/.docker/machine/certs
local-docker-machine create local-cert-gen
local-docker-machine start local-by-flywheel
local-docker-machine regenerate-certs -f local-by-flywheel
local-docker-machine rm -f local-cert-gen

These steps caused Local by Flywheel to recognize that the Boot2Docker ISO was out of date. It triggered a download of the new version, and gave the output below. When it completed Local by Flywheel worked as expected. Whew!

bash-3.2$ alias local-docker-machine="/Applications/Local\ by\ Flywheel.app/Contents/Resources/extraResources/virtual-machine/vendor/docker/osx/docker-machine"
bash-3.2$
bash-3.2$ local-docker-machine stop local-by-flywheel; rm -rf ~/.docker/machine/certs; local-docker-machine create local-cert-gen; local-docker-machine start local-by-flywheel; local-docker-machine regenerate-certs -f local-by-flywheel; local-docker-machine rm -f local-cert-gen;
Stopping "local-by-flywheel"...
Machine "local-by-flywheel" is already stopped.
Creating CA: /Users/richb/.docker/machine/certs/ca.pem
Creating client certificate: /Users/richb/.docker/machine/certs/cert.pem
Running pre-create checks...
(local-cert-gen) Default Boot2Docker ISO is out-of-date, downloading the latest release...
(local-cert-gen) Latest release for github.com/boot2docker/boot2docker is v18.09.1
(local-cert-gen) Downloading /Users/richb/.docker/machine/cache/boot2docker.iso from https://github.com/boot2docker/boot2docker/releases/download/v18.09.1/boot2docker.iso...
(local-cert-gen) 0%....10%....20%....30%....40%....50%....60%....70%....80%....90%....100%
Creating machine...
(local-cert-gen) Copying /Users/richb/.docker/machine/cache/boot2docker.iso to /Users/richb/.docker/machine/machines/local-cert-gen/boot2docker.iso...
(local-cert-gen) Creating VirtualBox VM...
(local-cert-gen) Creating SSH key...
(local-cert-gen) Starting the VM...
(local-cert-gen) Check network to re-create if needed...
(local-cert-gen) Waiting for an IP...
Waiting for machine to be running, this may take a few minutes...
Detecting operating system of created instance...
Waiting for SSH to be available...
Detecting the provisioner...
Provisioning with boot2docker...
Copying certs to the local machine directory...
Copying certs to the remote machine...
Setting Docker configuration on the remote daemon...
Checking connection to Docker...
Docker is up and running!
To see how to connect your Docker Client to the Docker Engine running on this virtual machine, run: /Applications/Local by Flywheel.app/Contents/Resources/extraResources/virtual-machine/vendor/docker/osx/docker-machine env local-cert-gen
Starting "local-by-flywheel"...
(local-by-flywheel) Check network to re-create if needed...
(local-by-flywheel) Waiting for an IP...
Machine "local-by-flywheel" was started.
Waiting for SSH to be available...
Detecting the provisioner...
Started machines may have new IP addresses. You may need to re-run the `docker-machine env` command.
Regenerating TLS certificates
Waiting for SSH to be available...
Detecting the provisioner...
Copying certs to the local machine directory...
Copying certs to the remote machine...
Setting Docker configuration on the remote daemon...
About to remove local-cert-gen
WARNING: This action will delete both local reference and remote instance.
Successfully removed local-cert-gen