SQLite - Date

About

The date data type in SQLite context does not exist. It is not an affinity.

SQLite are capable of storing dates and times with the following data type (affinity) as:

• TEXT with ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).
• REAL as Julian day numbers (the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar)
• or INTEGER values as Unix Time (the number of seconds since 1970-01-01 00:00:00 UTC)

You use then the sqlite date function to manipulate them.

SQLite transforms any date value to UTC+0000.

For instance, you can store a date as a string of:

• YYYY-MM-DD ie YEAR-MONTH-DAY
• DDDDDDDDDD ie a julian date number (can be a number then)

Management

Init

See the Date Functions

select date(time-value, "modifier", "modifier", ...)
select time(time-value, "modifier", "modifier", ...)
select datetime(time-value, "modifier", "modifier", ...)
...

where the date function:

• date returns the date string %Y-%m-%d
• time returns the time string %H:%M:%S
• datetime returns the date time string %Y-%m-%d %H:%M:%S
• julianday returns the julian date number (ie %J)
• strftime permits to specify further the returned format

where the time-value may be:

• a string:
  • date (YYYY-MM-DD),
  • datetime (YYYY-MM-DD HH:MM, YYYY-MM-DD HH:MM:SS, YYYY-MM-DD HH:MM:SS.SSS, YYYY-MM-DDTHH:MM, YYYY-MM-DDTHH:MM:SS, YYYY-MM-DDTHH:MM:SS.SSS)
  • time (HH:MM, HH:MM:SS, HH:MM:SS.SSS)
  • now
• DDDDDDDDDD is:
  • by default a julian date number expressed as an integer or floating point value
  • an epoch if the modifier unixepoch is used

Example: the following string are the same date

select date("2018-01-01")
select date("2458119.5") -- Julian number

Add / Substract

See the modifiers of the Date Functions

• NNN days
• NNN months
• NNN years

Example:

• min one day

select date("2018-01-01",'-1 days')

2017-12-31

• Plus one month

select date("2018-01-01",'+1 months')

2018-02-01

• Plus two year

select date("2018-01-01",'2 years')

2020-01-01

Diff

• Day level with Julian day

 SELECT julianday(first) - julianday(second) 

• Second level with strftime. You get the number of seconds since epoch time, you make a diff and you transform them in days.

SELECT (strftime('%s','2018-01-22T00:01:29') - strftime('%s','2018-01-20T00:01:29'))/60/60/24;

2 

Get weekday names in English

Sqlite does not handle the week day name. You need to get the day of the week with the %w strftime format modifier and wrap it in a case statement

Example

select case cast(strftime('%w', date()) as integer)
           when 0 then 'Sunday'
           when 1 then 'Monday'
           when 2 then 'Tuesday'
           when 3 then 'Wednesday'
           when 4 then 'Thursday'
           when 5 then 'Friday'
           else 'Saturday' end as english_weekday

Date function

See the modifiers of the date Functions

• start of month
• start of year
• start of day
• weekday N - Nth day of the date week
• unixepoch ie Time - (Unix|POSIX|Epoch) time
• localtime ie time in your timezone
• utc What is the UTC standard (Coordinated Universal Time) ?

Example:

• Start of month

select date("2018-01-02",'start of month')

2018-01-01

• The second day (1 = Monday) of the week of the date

select date("2018-01-02",'weekday 2')

2018-01-02 -- A tuesday

Format / Output

strftime

• strftime takes a format and a valid sqlite date string (ie 2018-01-01 will work but not 01-01-2018)

select strftime("%m-%d-%Y","2018-01-01")

01-01-2018

from Epoch (Jdbc)

If your date is stored in epoch format, you can use the datetime function

Example

• in the GMT zone.

select datetime(myDateColumn/1000,'unixepoch') from myTable

• in local zone

select datetime(myDateColumn/1000,'unixepoch','localtime') from myTable

Query Example

• All redirections from the last 5 minutes:

select 
  * 
from 
  redirections_log 
where 
  datetime(timestamp) > datetime('now','-5 minutes')
order by 
  datetime(timestamp) desc;

• Count of redirections by minutes

select 
  strftime('%H:%M', timestamp), 
  count(1) 
from 
  redirections_log 
group by 
  strftime('%H:%M', timestamp)

Documentation / Reference

• Function
• date and time data type