Small. Fast. Reliable.
Choose any three.

About Sitemap Documentation Download License News Developers Support

## SQL As Understood By SQLite

### Date And Time Functions

SQLite supports five date and time functions as follows:

1. date(timestring, modifier, modifier, ...)
2. time(timestring, modifier, modifier, ...)
3. datetime(timestring, modifier, modifier, ...)
4. julianday(timestring, modifier, modifier, ...)
5. strftime(format, timestring, modifier, modifier, ...)

All five 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.

The date() function returns the date in this format: YYYY-MM-DD. The time() function returns the time as HH:MM:SS. The datetime() function returns "YYYY-MM-DD HH:MM:SS". The julianday() function returns the julian day - number of days since noon in Greenwich on November 24, 4714 B.C. ( Proleptic Gregorian calendar). The strftime() routine returns the date formatted according to the format string specified as the first argument. The format string supports the most common substitutions found in the strftime() function from the standard C library plus two new substitutions, %f and %J. The following is a complete list of valid strftime() substitutions:

> %d |  | day of month: 00
> %f |  | fractional seconds: SS.SSS
> %H |  | hour: 00-24
> %j |  | day of year: 001-366
> %J |  | Julian day number
> %m |  | month: 01-12
> %M |  | minute: 00-59
> %s |  | seconds since 1970-01-01
> %S |  | seconds: 00-59
> %w |  | day of week 0-6 with sunday==0
> %W |  | week of year: 00-53
> %Y |  | year: 0000-9999
> %% |  | %

Notice that all other date and time functions can be expressed in terms of strftime():

> Function |  | Equivalent strftime()
> date(...) |  | strftime("%Y-%m-%d", ...)
> time(...) |  | strftime("%H:%M:%S", ...)
> datetime(...) |  | strftime("%Y-%m-%d %H:%M:%S", ...)
> julianday(...) |  | strftime("%J", ...)

The only reasons for providing functions other than strftime() is for convenience and for efficiency.

### Time Strings

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

1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DD T HH:MM
6. YYYY-MM-DD T HH:MM:SS
7. YYYY-MM-DD T HH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD

In formats 5 through 7, the "T" is a literal character separating the date and the time, as required by ISO-8601. Formats 8 through 10 that specify only a time assume a date of 2000-01-01. Format 11, the string 'now', is converted into the current date and time as obtained from the xCurrentTime method of the sqlite3_vfs object in use. Universal Coordinated Time (UTC) is used. Format 12 is the julian day number expressed as a floating point value.

### Modifiers

The time string can be followed by zero or more modifiers that alter the date or alter the interpretation of the date. The available modifiers are as follows.

1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10. weekday N
11. unixepoch
12. localtime
13. utc

The first six modifiers (1 through 6) simply add the specified amount of time to the date specified by the preceding timestring.

The "start of" modifiers (7 through 9) shift the date backwards to the beginning of the current month, year or day.

The "weekday" modifier advances the date forward to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth.

The "unixepoch" modifier (11) only works if it immediately follows a timestring in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a julian day number as it normally would be, but as Unix Time - the number of seconds since 1970.

The "localtime" modifier (12) adjusts the previous time string so that it displays the correct local time. "utc" undoes this.

### Examples

Compute the current date.

> SELECT date('now');

Compute the last day of the current month.

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

Compute the date and time given a unix timestamp 1092941466.

> SELECT datetime(1092941466, 'unixepoch');

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

> SELECT datetime(1092941466, 'unixepoch', 'localtime');

Compute the current unix timestamp.

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

Compute the number of days since the signing of the US Declaration of Independent.

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

Compute the number of seconds since a particular moment in 2004:

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

Compute the date of the first Tuesday in October for the current year.

> SELECT date('now','start of year','+9 months','weekday 2');

Compute the time since the unix epoch in seconds (like strftime('%s','now') except includes fractional part):

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

### Caveats And Bugs

The computation of local time depends heavily on the whim of politicians and is thus difficult to get correct for all locales. In this implementation, the standard C library function localtime_r() is used to assist in the calculation of local time. The localtime_r() C function normally only works for years between 1970 and 2037. For dates outside this range, SQLite attempts to map the year into an equivalent year within this range, do the calculation, then map the year back.

Date computations do not give correct results for dates before Julian day number 0 (-4713-11-24 12:00:00).

Non-Vista Windows platforms only support one set of DST rules. Vista only supports two. Therefore, on these platforms, historical DST calculations will be incorrect. For example, in the US, in 2007 the DST rules changed. Non-Vista Windows platforms apply the new 2007 DST rules to all previous years as well. Vista does somewhat better getting results correct back to 1986, when the rules were also changed.

All internal computations assume the Gregorian calendar system. It is also assumed that every day is exactly 86400 seconds in duration.

This page last modified 2008/08/12 18:08:17 UTC