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:

Management

Init

See the Date Functions

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

where the date function may be:

  • 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

You got the julian number with select julianday(date(“2018-01-01”))

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

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 

Date function

See the modifiers of the date Functions

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

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

As java/sql/Date stores the epoch date with milliseconds in epoch format, you need to divide by 1000

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


Powered by ComboStrap