English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية

SQLite basic tutorial

SQLite advanced tutorial

SQLite program interface

SQLite Date and Time

SQLite supports the following five date and time functions:

Serial numberFunctionExample
1date(timestring, modifier, modifier, ...)Starting with YYYY-MM-Returned in the format DD.
2time(timestring, modifier, modifier, ...)Returned in the format HH:MM:SS.
3datetime(timestring, modifier, modifier, ...)Starting with YYYY-MM-Returned in the format DD HH:MM:SS.
4julianday(timestring, modifier, modifier, ...)This will return the number of days from the year before the Common Era to Greenwich Mean Time. 4714 Year 11 Month 24 Days from noon on the day.
5strftime(format, timestring, modifier, modifier, ...)This will return a formatted date based on the format string specified by the first parameter. Specific formats are explained below.

The above five date and time functions take a time string as a parameter. A time string is followed by zero or more modifier modifiers. The strftime() function can also take a format string format as its first parameter. Below, we will explain in detail different types of time strings and modifiers.

Time string

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

Serial numberTime 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 "T" as the character to separate dates and times.

Modifier (Modifier)

Time strings can be followed by zero or more modifiers, which will change the dates and times returned by the above five functions./Or time. Any of the five above functions return time. Modifiers should be used from left to right, and the following list shows the modifiers that can be used 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

format

SQLite provides very convenient functions strftime() to format any date and time. You can use the following replacements to format date and time:

ReplacementDescription
%dDay of the month, 01-31
%fFractional seconds, SS.SSS
%HHour, 00-23
%jDay of the year, 001-366
%JJulian day number, DDDD.DDDD
%mMonth, 00-12
%MMinutes, 00-59
%sFrom 1970-01-01 Seconds since midnight
%SSeconds, 00-59
%wDay of the week, 0-6 (0 is Sunday)
%WWeek number of the year, 01-53
%YYear, YYYY
%%% symbol

Example

Now let's use the SQLite prompt to try various examples. The following command calculates the current date.

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

The following command calculates the last day of the current month.

+1　month', '',-1　day');
2013-05-31

The following command calculates the date and time for a given UNIX timestamp1092941466of the date and time.

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

The following command calculates the date and time for a given UNIX timestamp1092941466of the date and time, and compensates for your local timezone.

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

The following command calculates the current UNIX timestamp.

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

The following command calculates the number of days since the signing of the American Declaration of Independence.

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

The following command calculates the time since2004seconds since a specific moment in the year.

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

The following command calculates the year's10The date of the first Tuesday of the month.

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

The following commands calculate time since the UNIX epoch in seconds (similar to strftime('%s', 'now'), but including the decimal part).

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

To convert between UTC and localtime values when formatting dates, use the utc or localtime modifier as shown below:

sqlite> SELECT time('12:00', 'localtime');
05:00:00
sqlite> SELECT time('12:00', 'utc');
19:00:00
SQLite Common FunctionsSQLite Vacuum