sr.no.	function	example
1	date(timestring, modifiers...)	this returns the date in this format: yyyy-mm-dd
2	time(timestring, modifiers...)	this returns the time as hh:mm:ss
3	datetime(timestring, modifiers...)	this returns yyyy-mm-dd hh:mm:ss
4	julianday(timestring, modifiers...)	this returns the number of days since noon in greenwich on november 24, 4714 b.c.
5	strftime(timestring, modifiers...)	this returns the date formatted according to the format string specified as the first argument formatted as per formatters explained below.

all the above five date and time functions take a time string as an argument. the time string is followed by zero or more modifiers. the strftime() function also takes a format string as its first argument. following section will give you detail on different types of time strings and modifiers.

time strings

a time string can be in any of the following formats −

sr.no.	time string	example
1	yyyy-mm-dd	2010-12-30
2	yyyy-mm-dd hh:mm	2010-12-30 12:10
3	yyyy-mm-dd hh:mm:ss.sss	2010-12-30 12:10:04.100
4	mm-dd-yyyy hh:mm	30-12-2010 12:10
5	hh:mm	12:10
6	yyyy-mm-ddthh:mm	2010-12-30 12:10
7	hh:mm:ss	12:10:01
8	yyyymmdd hhmmss	20101230 121001
9	now	2013-05-07

you can use the "t" as a literal character separating the date and the time.

modifiers

the time string can be followed by zero or more modifiers that will alter date and/or time returned by any of the above five functions. modifiers are applied from the left to right.

following modifers are available in sqlite −

nnn days
nnn hours
nnn minutes
nnn.nnnn seconds
nnn months
nnn years
start of month
start of year
start of day
weekday n
unixepoch
localtime
utc

formatters

sqlite provides a very handy function strftime() to format any date and time. you can use the following substitutions to format your date and time.

substitution	description
%d	day of month, 01-31	%f	fractional seconds, ss.sss	%h	hour, 00-23	%j	day of year, 001-366	%j	julian day number, dddd.dddd	%m	month, 00-12	%m	minute, 00-59	%s	seconds since 1970-01-01	%s	seconds, 00-59	%w	day of week, 0-6 (0 is sunday)	%w	week of year, 01-53	%y	year, yyyy	%%	% symbol

examples

let's try various examples now using sqlite prompt. following command computes the current date.

sqlite> select date('now');
2013-05-07

following command computes the last day of the current month.

sqlite> select date('now','start of month','+1 month','-1 day');
2013-05-31

following command computes the date and time for a given unix timestamp 1092941466.

sqlite> select datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06

following command computes the date and time for a given unix timestamp 1092941466 and compensate for your local timezone.

sqlite> select datetime(1092941466, 'unixepoch', 'localtime');
2004-08-19 13:51:06

following command computes the current unix timestamp.

sqlite> select strftime('%s','now');
1393348134

following command computes the number of days since the signing of the us declaration of independence.

sqlite> select julianday('now') - julianday('1776-07-04');
86798.7094695023

following command computes the number of seconds since a particular moment in 2004.

sqlite> select strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
295001572

following command computes the date of the first tuesday in october for the current year.

sqlite> select date('now','start of year','+9 months','weekday 2');
2013-10-01

following command computes the time since the unix epoch in seconds (like strftime('%s','now') except includes fractional part).

sqlite> select (julianday('now') - 2440587.5)*86400.0;
1367926077.12598

to convert between utc and local time values when formatting a date, use the utc or localtime modifiers as follows −

sqlite> select time('12:00', 'localtime');
05:00:00

sqlite> select time('12:00', 'utc');
19:00:00

Previous Lesson

Next Lesson