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 as:

  • TEXT,
  • REAL,
  • or INTEGER values

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("string")

where the date parameter is a string that can be:

Example: the following string are the same date

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

I 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