SQLite Datetime Functions

SQLite, unlike most other versions of SQL treats dates and time like a string. Frankly, datetime really is nothing more than a string with a mask. SQLite implements enough of the functions to allow you to manipulate datetime values.

Get Current Date / Time / Datetime

 
SELECT 
  datetime('now'),
  date('now'),
  time('now')
;

N Days In Past/Future

SELECT date('now'), date('now', '1 day'), date('now', '-1 day'), date('now', '90 day') ;

N Months In Past/Future

 
SELECT 
  date('now'),
  date('now', '1 month'),
  date('now', '-1 month'),
  date('now', '-1000 month')
;

N Years In Past/Future

 
SELECT 
  date('now'),
  date('now', '1 year'),
  date('now', '-1 year'),
  date('now', '-1000 year')
;

Date Math

 
SELECT 
  date('now'),
  julianday('now') - julianday('2017-01-01')
;

Start of Day / Month / Year

 
SELECT 
  datetime('now'),
  --weekday(date('now')),
  datetime('now','start of day'),
  date('now','start of month'),
  date('now','start of year')
;

Using strftime() to Determine Number of Days Between Two Dates

Parenthesis matter when using SQLite.

 
SELECT 
  datetime('now'),
  ( ( strftime('%s','now') - strftime('%s','2017-01-01 00:00:00') ) / 3600) / 24
;

Get Day of Week | Get Week of Year

 
SELECT 
  datetime('now'),
  strftime('%w','now') as "Day of Week", -- 0=Sunday
  strftime('%W','now') as "Week of Year"
;

Documentation

List of strftime() Substitutions

 
%d		day of month: 00
%f		fractional seconds: SS.SSS
%H		hour: 00-24
%j		day of year: 001-366
%J		Julian day number
%m		month: 01-12
%M		minute: 00-59
%s		seconds since 1970-01-01
%S		seconds: 00-59
%w		day of week 0-6 with Sunday==0
%W		week of year: 00-53
%Y		year: 0000-9999
%%		%

Equivalent strftime Functions

Function		    Equivalent strftime()
----------------    ----------------------------------
date(...)		    strftime('%Y-%m-%d', ...)
time(...)		    strftime('%H:%M:%S', ...)
datetime(...)		strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...)		strftime('%J', ...)

Datetime Strings

 
YYYY-MM-DD
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
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

Datetime Modifiers

 
N days
N hours
N minutes
N.NNN seconds
N months
N years
start of month
start of year
start of day
weekday N
unixepoch
localtime
utc