SQLite Date & Time

SqliteDate & Time

SQLite supports the following five date and time functions:

Serial Number	function	Example
1	Date (timestring, modifiers ...)	Returns the date in YYYY-MM-DD format.
2	Time (timestring, modifiers ...)	Returns the time in HH:MM:SS format.
3	DateTime (timestring, modifiers ...)	Returned in YYYY-MM-DD HH:MM:SS format.
4	Julianday (timestring, modifiers ...)	This returns the number of days from noon November 24, 4714 BC.
5	Strftime (timestring, modifiers ...)	This returns the formatted date based on the format string specified by the first parameter. The specific format is explained below.

G, modifiers ...)

This returns the formatted date based on the format string specified by the first parameter. The specific format is explained below.

The five date and time functions above take a time string as a parameter. A time string is followed by 0 or more modifiers modifiers. The strftime () function can also take a format string as its first argument. The different types of time strings and modifiers are explained in detail below.

Time string

A time string can take any of the following formats:

Serial Number	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 "T" as a literal character separating the date and time.

Modifier (Modifiers)

The time string can be followed by 0 or more modifiers, which will change the date and/or time returned by the above five functions. Any of the above five major functions return time. Modifiers should be used from left to right, and the modifiers that can be used in SQLite are listed below:

• 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

Formatting

SQLite provides a very handy function strftime () to format any date and time. You can use the following substitutions to format the date and time:

Replace	Description
%d	The day of January, 01-31
%f	Seconds with a decimal point, SS. Sss
%H	Hours, 00-23
%j	The first day of the year, 001-366
%J	The number of Julian days, DDDD. DDDD
%m	Month, 00-12
%M	Points, 00-59
%s	Number of seconds since 1970-01-01
%s	Seconds, 00-59
%w	Day of the week, 0-6 (0 is Sunday)
%W	Week of the year, 01-53
%Y	Years, YYYY
%%	% symbol

Instance

Now let's try different instances using the SQLite prompt. The following is the calculation of the current date:

SQLite> SELECT date(' Now ');  ----         

Here's the last day to calculate the current month:

SQLite> SELECT date(' Now ',' start of month ',' +1 month ','-1 day ');  ----              

The following is the date and time to calculate the given UNIX timestamp 1092941466:

SQLite> SELECT datetime(1092941466,' Unixepoch ');  2004---£º       

The following is the date and time for calculating a given UNIX timestamp 1092941466 relative to the local time zone:

SQLite> SELECT datetime(1092941466,' Unixepoch ',' localtime ');  2004--All   :    : 

The following is the calculation of the current UNIX timestamp:

SQLite> SELECT datetime(1092941466,' Unixepoch ',' localtime ');  1367926057         

Here are the days since the United States Declaration of Independence was signed:

SQLite> SELECT julianday(' Now ')- julianday(' 1776-07-04 ');  86504.4775830326          

Here is the number of seconds to calculate from a specific time in 2004:

SQLite> SELECT strftime('%s ',' Now ')- strftime('%s ',' 2004-01-01 02:34:56 '); 295001572              

The following is the date for calculating the first Tuesday of the current year October:

SQLite> SELECT date(' Now ',' start of Year ', '+9 months ',' weekday 2 ');  Ten--- -             

The following is the calculation of the time in seconds from the UNIX era (similar to Strftime ('%s ', ' Now '), which is different from the number of decimal parts included):

SQLite>(julianday(' Now ')-2440587.5) *86400.0;  1367926077.12598           

Convert between UTC and local time values, using the UTC or localtime modifier when formatting the date, as follows:

SQLite> SELECT time(' 12:00pm ',' localtime ');  xx:            xx

SQLite>  SELECT time(' 12:00pm ',' UTC ');  :xx           

SQLite Date & Time