Blog

SQLite – Date & Time

SQLite supports five date and time functions as follows −

Sr.No.FunctionExample
1date(timestring, modifiers…)This returns the date in this format: YYYY-MM-DD
2time(timestring, modifiers…)This returns the time as HH:MM:SS
3datetime(timestring, modifiers…)This returns YYYY-MM-DD HH:MM:SS
4julianday(timestring, modifiers…)This returns the number of days since noon in Greenwich on November 24, 4714 B.C.
5strftime(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 StringExample
1YYYY-MM-DD2010-12-30
2YYYY-MM-DD HH:MM2010-12-30 12:10
3YYYY-MM-DD HH:MM:SS.SSS2010-12-30 12:10:04.100
4MM-DD-YYYY HH:MM30-12-2010 12:10
5HH:MM12:10
6YYYY-MM-DDTHH:MM2010-12-30 12:10
7HH:MM:SS12:10:01
8YYYYMMDD HHMMSS20101230 121001
9now2013-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.

SubstitutionDescription
%dDay of month, 01-31
%fFractional seconds, SS.SSS
%HHour, 00-23
%jDay of year, 001-366
%JJulian day number, DDDD.DDDD
%mMonth, 00-12
%MMinute, 00-59
%sSeconds since 1970-01-01
%SSeconds, 00-59
%wDay of week, 0-6 (0 is Sunday)
%WWeek of year, 01-53
%YYear, 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

Leave a Reply