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
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
- 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
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
As javase/9/docs/api/java/sql/Date.html 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)