SQLite CVSTrac

Page History

By DRH on 2003-09-28

The document describes proposed date and time functions for SQLite.

There are experimental date and time functions in SQLite now. But they have proven to be inadequate. The proposal here incorporates lessons learned from the existing date and time functions.

Function Overview

Five date and time functions are proposed, as follows:

date( timestring, modifier, modifier, ...)
time( timestring, modifier, modifier, ...)
datetime( timestring, modifier, modifier, ...)
julianday( timestring, modifier, modifier, ...)
strftime( format, timestring, modifier, modifier, ...)

All five functions take a time string as an argument. This time string may be 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 number of days since noon in Greenwich on November 24, 4714 B.C. The julian day number is the preferred internal representation of dates. The strftime() routine returns the date formatted according to the format string specified as the first argument. The format string supports most, but not all, of the more comment substitutions found in the strftime() function from the standard C library:

   %d  day of month
   %f  ** fractional seconds  SS.SSS
   %H  hour 00-24
   %j  day of year 000-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  sunday==0
   %W  week of year 00-53
   %Y  year 0000-9999
   %%  %

The %f and %J conversions are new. Notice that all of the other four functions could be expressed in terms of 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:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDD.DDDD

Formats 5 through 7 that specify only a time assume a date of 2000-01-01. Format 8, the string 'now', is converted into the current date and time. Universal Coordinated Time (UTC) is used. Format 9 is the julian day number expressed as a floating point value.

Please be sure to address Ticket #415 so we get full millisecond precision in julian day numbers. Unfortunately this may mean adjusting some expression test cases that depend on values rounded to fewer digits. -- e

Modifiers

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

NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN months (see #551)
NNN years (see #551)
start of month
start of year
start of week (withdrawn -- will not be implemented)
start of day
weekday N (see #551)
unixepoch
localtime
utc
julian (not implemented as of 5 Jan 2004)
gregorian (not implemented as of 5 Jan 2004)

The first size 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 10) shift the date backwards to the beginning of the current month, year, week, 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 (12) only works if it immediately follows a timestring in the DDDD.DDDDD format. This modifier causes the DDDD.DDDDD to be interpreted not as a julian day number as it normally would be, but as the number of seconds since 1970. This modifier allows unix-based times to be converted to julian day numbers easily.

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

The "julian" modifier (14) assumes the that the time string is a gregorian date and converts the date into a julian date. "gregorian" undoes the work of "julian".

Examples

Compute the current date.

  SELECT date('now');

Compute the last day of the current month.

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

Compute the date and time given a unix timestamp X.

  SELECT datetime(X, 'unixepoch');

Compute the current unix timestamp.

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

Compute the number of days since the battle of Hastings.

  SELECT julianday('now') - julianday('1066-10-14','gregorian');

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

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

Caveats And Bugs

The computation of local time depends heavily on the whim of local politicians and is thus difficult to get correct for all locales. In this implementation, the standard C library function localtime() is used to assist in the calculation of local time. Note that localtime() is not threadsafe, so use of the "localtime" modifier is not threadsafe. Also, the localtime() 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.

Please surround uses of localtime() with sqliteOsEnterMutex() and sqliteOsLeaveMutex() so threads using SQLite are protected, at least! -- e

All internal computations assume the Gregorian calendar system. When you use the "julian" modifier, it does not convert the date into a real Julian calendar date, it merely shifts the Gregorian calendar date to

align it with the Julian calendar. This means that the "julian" modifier will not work right for dates that exist in the Julian calendar but which do not exist in the Gregorian calendar. Example: 1900-02-29.

sqlite - Date And Time Functions

Page History