Easy Tutorial
Sqlite Alias Sqlite Like Clause Sqlite Syntax Sqlite Php Sqlite Distinct Keyword Sqlite Functions Sqlite Autoincrement Sqlite Detach Database Sqlite Unions Clause Sqlite Attach Database Sqlite Joins Sqlite Pragma Sqlite Group By Sqlite Where Clause Sqlite Injection Sqlite And Or Clauses Sqlite Operators Sqlite Constraints Sqlite Drop Table Sqlite C Cpp Sqlite Order By Sqlite Subquery Sqlite Expressions Sqlite View Sqlite Indexed By Sqlite Alter Command Sqlite Insert Sqlite Select Sqlite Explain Sqlite Python Sqlite Installation Sqlite Transaction Sqlite Update Sqlite Delete Sqlite Commands Sqlite Date Time Sqlite Null Values Sqlite Create Table Sqlite Java Sqlite Data Types Sqlite Trigger Sqlite Create Database Sqlite Limit Clause Sqlite Intro Sqlite Truncate Table Sqlite Vacuum Sqlite Perl Sqlite Glob Clause Sqlite Index Sqlite Tutorial Sqlite Having Clause
❮ Sqlite Commands Sqlite Null Values ❯

SQLite Date & Time

SQLite supports the following five date and time functions:

No. Function Example
1 date(timestring, modifier, modifier, ...) Returns the date in YYYY-MM-DD format.
2 time(timestring, modifier, modifier, ...) Returns the time in HH:MM:SS format.
3 datetime(timestring, modifier, modifier, ...) Returns in YYYY-MM-DD HH:MM:SS format.
4 julianday(timestring, modifier, modifier, ...) This returns the number of days since noon in Greenwich on November 24, 4714 B.C.
5 strftime(format, timestring, modifier, modifier, ...) This returns the formatted date according to the format string specified in the first parameter. See details below.

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 can also take a format string as its first parameter. Below, we will explain different types of time strings and modifiers in detail.

Time Strings

A time string can be in any of the following formats:

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 12-30-2010 12:10
5 HH:MM 12:10
6 YYYY-MM-DD THH: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 to separate the date and time.

Modifiers

A time string can be followed by zero or more modifiers, which will change the date and/or time returned by the above five functions. Modifiers should be used from left to right. Below are the modifiers available in SQLite:

Formatting

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

Examples

Now let's try different examples using the SQLite prompt. Below is the calculation of the current date:

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

Below is the calculation of the last day of the current month:

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

Below is the calculation of the date and time for a given UNIX timestamp 1092941466:

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

Below is the calculation of the date and time for a given UNIX timestamp 1092941466 relative to the local time zone:

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

Below is the calculation of the current UNIX timestamp:

sqlite> SELECT strftime('%s','now');
1367926057

Below is the calculation of the days since the signing of the U.S. "Declaration of Independence":

sqlite> SELECT julianday('now') - julianday('1776-07-04');
86504.4775830326

Below is the calculation of the seconds since a particular moment in 2004:

sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 00:00:00');

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

Below is the calculation for the date of the first Tuesday in October of the current year:

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

Below is the calculation for the time in seconds since the UNIX epoch (similar to strftime('%s','now'), but includes fractional parts):

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

To convert between UTC and local time values when formatting dates, use the 'utc' or 'localtime' modifiers as shown below:

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

sqlite> SELECT time('12:00', 'utc');
19:00:00
❮ Sqlite Commands Sqlite Null Values ❯