Sqlite - Time

Sqlite

About

Sqlite does not support natively the storage of a date/time data type.

SQLite and Time

Type affinity

By default, the type affinity for a time is a numeric.

In the Date Time affinity example, you can see that a date or datetime becomes a numeric

SQLite Time recommendation

SQLite lets you choose between text and number (see date and time datatype in Sqlite. ie

  • 2020-10-14 21:06:46.507 (Iso string)
  • 1602702406 (Epoch Seconds called unixepoch in the date time function of Sqlite)

Tabulify Default

By default, tabulify has chosen to save the date in the text format (ie SqlLiteral) because:

You can control this behavior by setting the time configuration built-in attributes

There is one caveat:

  • the type name should be a known time name (ie date, time, timestamp, …) so that Tabulify can see them as time data type.
  • unfortunately, it means that you can't create a table with the create table as statement. Why? because SQLite translates them to the numeric affinity. See the support section.

Example

With tabulify, by default, you would be able to manipulate them with the Sqlite Date Time function like that:

  • Date
SELECT date('2020-10-15','-1 day')
-- or 
SELECT date(dateColumn,'-1 day') from table;
  • Timestamp (Datetime)
SELECT datetime('2020-10-15 16:34:56.607','-1 seconds')
-- or 
SELECT datetime(timestampColumn,'-1 seconds') from table;
  • Time
SELECT time('22:10:23','+1 seconds') 
-- or 
SELECT time(timeColumn,'+1 seconds')  from table;

Support

Time Zone Support

The Time zone data type (time and timestamp) got the default affinity of SQLite, ie numeric. We don't manipulate them at all, ie we don't store them as text.

Create Table As Caveat on the text time system

Unfortunately, when using a create table as statement, SQLite will automatically convert the data type to its affinity.

For instance, in this demo, we show you how a timestamp becomes a numeric 

tabul data cat sqlite/timestamp_to_num_demo.sql@howto

drop table if exists foo;
drop table if exists bar;
create table foo(update_time timestamp);
create table bar as
select *
from foo;
PRAGMA table_info('bar');


tabul data print '(sqlite/timestamp_to_num_demo.sql@howto)'@sqlite
  • The result, where you can see NUM in the type column and not the original timestamp name.
(sqlite/timestamp_to_num_demo.sql@howto)@sqlite
cid          name   type   notnull   dflt_value   pk
---   -----------   ----   -------   ----------   --
  0   update_time    NUM         0                 0
Task Runner