1. Overview
SQLite supports six date and time functions as follows:
- date(time-value, modifier, modifier, .)
- time(time-value, modifier, modifier, .)
- datetime(time-value, modifier, modifier, .)
- julianday(time-value, modifier, modifier, .)
- unixepoch(time-value, modifier, modifier, .)
- strftime(format, time-value, modifier, modifier, .)
All six date and time functions take an optional time value as an argument, followed by zero or more modifiers. The strftime() function also takes a format string as its first argument.
Date and time values can be stored as
- text in a subset of the ISO-8601 format,
- numbers representing the Julian day, or
- numbers representing the number of seconds since (or before) 1970-01-01 00:00:00 UTC (the unix timestamp).
All of the date time functions access time-values in any of the above time formats.
The date() function returns the date as text in this format: YYYY-MM-DD.
The time() function returns the time as text in this format: HH:MM:SS.
The datetime() function returns the date and time as text in their same formats: YYYY-MM-DD HH:MM:SS.
The julianday() function returns the Julian day — the fractional number of days since noon in Greenwich on November 24, 4714 B.C. (Proleptic Gregorian calendar).
The unixepoch() function returns a unix timestamp — the number of seconds since 1970-01-01 00:00:00 UTC. The unixepoch() always returns an integer, even if the input time-value has millisecond precision.
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 (fractional) %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 %% %
All other date and time functions can be expressed in terms of strftime():
Function Equivalent (or nearly) strftime() date(. ) strftime(‘%Y-%m-%d’, . ) time(. ) strftime(‘%H:%M:%S’, . ) datetime(. ) strftime(‘%Y-%m-%d %H:%M:%S’, . ) julianday(. ) unixepoch(. )
The date(), time(), and datetime() functions all return text, and so their strftime() equivalents are exact. However (note-1) the julianday() and unixepoch() functions return numeric values. Their strftime() equivalents return strings that is the text representation of the corresponding number.
The main reasons for providing functions other than strftime() are for convenience and for efficiency. The julianday() and unixepoch() functions return real and integer values respectively, and do not incur the format conversion costs or inexactitude resulting from use of the ‘%J’ or ‘%s’ format specifiers with the strftime() function.
2. Time Values
A time value can be in any of the following formats shown below. The value is usually a string, though it can be an integer or floating point number in the case of format 12.
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. The ‘now’ argument to date and time functions always returns exactly the same value for multiple invocations within the same sqlite3_step() call. Universal Coordinated Time (UTC) is used. Format 12 is the Julian day number expressed as an integer or floating point value. Format 12 might also be interpreted as a unix timestamp if it is immediately followed either the ‘auto’ or ‘unixepoch’ modifier.
Formats 2 through 10 may be optionally followed by a timezone indicator of the form «[+-]HH:MM» or just «Z«. The date and time functions use UTC or «zulu» time internally, and so the «Z» suffix is a no-op. Any non-zero «HH:MM» suffix is subtracted from the indicated date and time in order to compute zulu time. For example, all of the following time values are equivalent:
2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 04:23:19.120-04:00
2456572.84952685
In formats 4, 7, and 10, the fractional seconds value SS.SSS can have one or more digits following the decimal point. Exactly three digits are shown in the examples because only the first three digits are significant to the result, but the input string can have fewer or more than three digits and the date/time functions will still operate correctly. Similarly, format 12 is shown with 10 significant digits, but the date/time functions will really accept as many or as few digits as are necessary to represent the Julian day number.
The time-value (and all modifiers) may be omitted, in which case a time value of ‘now’ is assumed.
3. Modifiers
The time value can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows.
- 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
- julianday
- auto
- localtime
- utc
The first six modifiers (1 through 6) simply add the specified amount of time to the date and time specified by the arguments to the left. The ‘s’ character at the end of the modifier names is optional. Note that «±NNN months» works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the date 2001-03-31 modified by ‘+1 month’ initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four.
The «start of» modifiers (7 through 9) shift the date backwards to the beginning of the subject month, year or day.
The «weekday» modifier advances the date forward, if necessary, to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth. If the date is already on the desired weekday, the «weekday» modifier leaves the date unchanged.
The «unixepoch» modifier (11) only works if it immediately follows a time value 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. If the «unixepoch» modifier does not follow a time value of the form DDDDDDDDDD which expresses the number of seconds since 1970 or if other modifiers separate the «unixepoch» modifier from prior DDDDDDDDDD then the behavior is undefined. For SQLite versions before 3.16.0 (2017-01-02), the «unixepoch» modifier only works for dates between 0000-01-01 00:00:00 and 5352-11-01 10:52:47 (unix times of -62167219200 through 106751991167).
The «julianday» modifier must immediately follow the initial time-value which must be of the form DDDDDDDDD. Any other use of the ‘julianday’ modifier is an error and causes the function to return NULL. The ‘julianday’ modifier forces the time-value number to be interpreted as a julian-day number. As this is the default behavior, the ‘julianday’ modifier is scarcely more than a no-op. The only difference is that adding ‘julianday’ forces the DDDDDDDDD time-value format, and causes a NULL to be returned if any other time-value format is used.
The «auto» modifier must immediately follow the initial time-value. If the time-value is numeric (the DDDDDDDDDD format) then the ‘auto’ modifier causes the time-value to interpreted as either a julian day number or a unix timestamp, depending on its magnitude. If the value is between 0.0 and 5373484.499999, then it is interpreted as a julian day number (corresponding to dates between -4713-11-24 12:00:00 and 9999-12-31 23:59:59, inclusive). For numeric values outside of the range of valid julian day numbers, but within the range of -210866760000 to 253402300799, the ‘auto’ modifier causes the value to be interpreted as a unix timestamp. Other numeric values are out of range and cause a NULL return. The ‘auto’ modifier is a no-op for text time-values.
The ‘auto’ modifier can be used to work with date/time values even in cases where it is not known if the julian day number or unix timestamp formats are in use. The ‘auto’ modifier will automatically select the appropriate format. However, there is a region of ambiguity. Unix timestamps for the first 63 days of 1970 will be interpreted as julian day numbers. The ‘auto’ modifier is very useful when the dataset is guaranteed to not contain any dates within that region, but should be avoided for applications that might make use of dates in the opening months of 1970.
The «localtime» modifier (14) assumes the time value to its left is in Universal Coordinated Time (UTC) and adjusts that time value so that it is in localtime. If «localtime» follows a time that is not UTC, then the behavior is undefined. The «utc» modifier is the opposite of «localtime». «utc» assumes that the time value to its left is in the local timezone and adjusts that time value to be in UTC. If the time to the left is not in localtime, then the result of «utc» is undefined.
4. Examples
Compute the current date.
Compute the last day of the current month.
Compute the date and time given a unix timestamp 1092941466.
SELECT datetime(1092941466, ‘unixepoch’);
SELECT datetime(1092941466, ‘auto’); — Does not work for early 1970!
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.
Compute the number of days since the signing of the US Declaration of Independence.
Compute the number of seconds since a particular moment in 2004:
SELECT unixepoch() — unixepoch(‘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 with millisecond precision:
5. 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.
These functions only work for dates between 0000-01-01 00:00:00 and 9999-12-31 23:59:59 (julian day numbers 1721059.5 through 5373484.5). For dates outside that range, the results of these functions are undefined.
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. They also assume that every day is exactly 86400 seconds in duration; no leap seconds are incorporated.
Функции даты и времени в SQLite
Как известно, в базе данных SQLite нет типа данных для хранения даты или времени. Предполагается хранить дату и время либо в строковом поле, либо в виде числа, т.е. использовать один из трех вариантов:
- TEXT — Для хранения даты/времени в формате «YYYY-MM-DD HH:MM:SS.SSS» (подробнее см.ниже)
- REAL — Для записи даты/времени в виде числа — Юлианского дня
- INTEGER — Чтобы сохранить дату/время как время Unix (число секунд с 1970-01-01 00:00:00 UTC)
Так же хочу отметить, что если хранить дату/время в строковом виде и соблюдать правильный формат, то будут доступны операции сравнения, сортировка по полю с датой/временем, а так же будут корректно работать и функции, описанные ниже.
Функции даты и времени в SQLite
Для работы с датой и временем SQLite предлагает 5 встроенных функций:
- date(timestring, modifier, modifier, .)
- time(timestring, modifier, modifier, .)
- datetime(timestring, modifier, modifier, .)
- julianday(timestring, modifier, modifier, .)
- strftime(format, timestring, modifier, modifier, .)
Все пять функций даты и времени принимают в качестве аргумента строку времени. За строкой времени могут следовать один или несколько модификаторов. Функция strftime() function также принимает строку формата в качестве первого аргумента.
Функции даты и времени используют стандарт ISO-8601 для строк формата. Функция date() возвращает дату в формате: YYYY-MM-DD. Функция time() возвращает время в формате HH:MM:SS. Функция datetime() возвращает «YYYY-MM-DD HH:MM:SS». Функция julianday() возвращает Юлианский день — число дней, прошедших начиная с полудня понедельника, 1 января 4713 до н. э. юлианского календаря.
Функция strftime() возвращает дату, отформатированную в соответствии со строкой формата, указанной в качестве первого аргумента. Строка формата поддерживает основные замены, которые есть в функции strftime() из стандартной библиотеки C плюс еще 2 замены: %f и %J. Ниже список всех корректных замен функции strftime() в SQLite:
| %d | День месяца: 00 |
| %f | Доли секунды: SS.SSS |
| %H | час: 00-24 |
| %j | день года: 001-366 |
| %J | Юлианский день |
| %m | месяц: 01-12 |
| %M | минуты: 00-59 |
| %s | количество секунд с 1970-01-01 (unix timestamp) |
| %S | секунды: 00-59 |
| %w | день недели 0-6 где Воскресенье==0 |
| %W | неделя года: 00-53 |
| %Y | год: 0000-9999 |
| %% | % |
Обратите внимание, что все другие функции даты и времени могут быть выражены через strftime():
| Функция | Эквивалент strftime() |
| date(. ) | strftime(‘%Y-%m-%d’, . ) |
| time(. ) | strftime(‘%H:%M:%S’, . ) |
| datetime(. ) | strftime(‘%Y-%m-%d %H:%M:%S’, . ) |
| julianday(. ) | strftime(‘%J’, . ) |
Основная причина использования других функций вместо strftime() — это удобство и эффективность.
Строковое представление даты и времени в SQLite
Для того, чтобы SQlite правильно понимал и работал с датой (сортировал, сравнивал и т.д.), строка содержащая дату и время должна быть в одном из следующих форматов:
В форматах с 5 по 7 символ «T» означает разделитель даты и времени, как это требуется стандартом ISO-8601. В форматах с 8 по 10 указано только время, при этом считается, что дата равна 2000-01-01. Формат 11 — строка ‘now’ преобразуется в текущую дату и время как полученные методом xCurrentTime объекта sqlite3_vfs. Значение ‘now’ функций даты и времени всегда возвращают одно и то же значение для нескольких вызовов в пределах одного и того же sqlite3_step(). Формат 12 — это Юлианский день выраженный в виде дробного числа.
Обратите внимание: Функции даты и времени в SQLite используют Всемирное координированное время (UTC). Чтобы получить локальные дату и время, следует использовать модификатор localtime, например, чтобы получить текущую локальную дату и время, можно воспользоваться таким запросом:
Форматы со 2 по 10 могут дополнительно сопровождаться индикатором часового пояса в формате «[+-]HH:MM» или просто указанием часовой зоны «Z«. Функции даты и времени используют UTC или «zulu» время (время по Гринвичу), таким образом суффикс «Z» не обязателен, если вы используете такое время. Любой не пустой суффикс «HH:MM» вычитается из указанной даты и времени для вычисления времени zulu. Например, все следующие строки эквивалентны:
2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 04:23:19.120-04:00
2456572.84952685
В форматах 4, 7 и 10 значение доли секунды SS.SSS может содержать одну или несколько цифр после запятой. В примерах показаны ровно три цифры, поскольку только первые три цифры значимы для результата, но входная строка может иметь меньше или больше трех цифр, при этом функции даты и времени будут работать правильно. Аналогично, формат 12 отображается с 10 значащими цифрами, но функции даты/времени действительно принимают столько цифр, сколько необходимо для представления числа в юлианский день.
Модификаторы даты и времени
За строкой времени может следовать один или более модификаторов, изменяющих дату и/или время. Каждый модификатор — это преобразование, которое применяется к значению времени слева от него. Модификаторы применяются слева направо, порядок важен. Доступны следующие модификаторы.
- 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
Первые шесть модификаторов (от 1 до 6) просто добавляют указанное количество времени к дате и времени, заданным предыдущим временем и модификаторами. Символ «s» в конце имен модификаторов является необязательным. Обратите внимание, что «±NNN months» работает путем преобразования оригинальной даты в формат YYYY-MM-DD, затем добавляется ±NNN месяцев к MM значению, затем результат нормализуется. Например, дата 2001-03-31 модифицированная с помощью ‘+1 month’ изначально дает 2001-04-31, но в апреле только 30 дней, поэтому дата нормализуется и становится 2001-05-01. Аналогичный эффект происходит когда дата February 29 високосного года и используется модификатор ±N years, где N не кратно четырем.
Модификаторы «start of» (с 7 по 9) сдвигает дату назад, на начало месяца, года или дня.
Модификатор «weekday» переносит дату вперед на следующую дату, где номер дня недели равен N. Воскресенье равно 0, Понедельник равен 1 и т.д.
Модификатор «unixepoch» (11) работает только в случае использования строки времени в формате DDDDDDDDDD. Этот модификатор заставляет dddddddddddd интерпретироваться не как номер Юлианского дня, как это обычно было бы, а как Unix Time — количество секунд с 1970. Если модификатор «unixepoch» получит значение в отличном от формата DDDDDDDDDD который будет означать количество секунд с 1970 или если предыдущие модификаторы, которые использованы до «unixepoch», преобразуют значение в отличное от DDDDDDDDDD тогда результат будет не корректным. В SQLite версий до 3.16.0 (2017-01-02), модификатор «unixepoch» работает только для дат от 0000-01-01 00:00:00 до 5352-11-01 10:52:47 (unix times с -62167219200 до 106751991167).
Модификатор «localtime» (12) ожидает, что строка времени слева является UTC и настраивает строку времени так, чтобы она отображала локальное время. Если «localtime» получает строку времени не в UTC, тогда результат будет не корректным. Модификатор «utc» — противоположность модификатору «localtime». «utc» ожидает, что срока слева — локальное время и преобразует его в UTC. Если строка не будет локальным временем, тогда «utc» вернет некорректный результат.
Примеры использования функций даты/времени в SQLite
Определить текущую дату.
Вычислить последний день текущего месяца.
Вычислить дату и время имея на входе метку времени unix 1092941466.
Вычислить дату и время имея на входе метку времени unix 1092941466, и перевести его в локальное время.
SELECT datetime(1092941466, ‘unixepoch’, ‘localtime’);
Получить текущую unix метку времени.
Вычислить количество дней с момента подписания Декларации Независимости США.
Вычислить количество секунд с определенного момента в 2004 году:
Вычислить дату первого вторника октября текущего года.
SELECT date(‘now’,’start of year’,’+9 months’,’weekday 2′);
Вычислить время с эпохи unix в секундах (аналогично strftime(‘%s’,’now’) не считая дробной части):
Предупреждения и ошибки
Вычисление местного времени в значительной степени зависит от прихоти политиков и, таким образом, трудно получить правильное время для всех часовых поясов. В этой реализации стандартная функция библиотеки C localtime_r() используется для вычисления местного времени. Функция localtime_r() обычно работает только в течение нескольких лет между 1970 и 2037. Для дат за пределами этого диапазона SQLite пытается сопоставить год с эквивалентным годом в пределах этого диапазона, выполнить расчет, а затем сопоставить год назад.
Все функции работают в пределах дат от 0000-01-01 00:00:00 до 9999-12-31 23:59:59 (юлианские дни от 1721059.5 до 5373484.5). Для даты вне этого диапазона, результаты этих функций не определены.
Не Vista платформы Windows поддерживают только один набор правил. Vista поддерживает только два. Поэтому на этих платформах исторические расчеты DST будут неверными. Например, в США, в 2007 году — правила перехода на летнее время изменились. Не Vista платформы Windows применят правила DST 2007 за все предыдущие годы. Vista делает несколько лучше получить результаты исправить обратно в 1986 году, когда правила были также изменены.
Все внутренние вычисления предполагают Григорианский календарь. Также предполагается, что каждый день содержит ровно 86400 секунд.