Sqlite Working with Dates and Times

 

In our sample database we have chosen to use integers for columns that store a date value, represented by the format YYYYMMDD. This format is fairly readable and, because the most significant part (the year) comes first, allows arithmetic comparisons to be performed. For instance just as February 29th 2004 is earlier than March 1st, 20040229 is a smaller number than 20040301.

This technique is not without its limitations. First, there is no validation on the values stored. Although February 29th is a valid date in the leap year 2004, it does not exist three years out of four and the value 20050229 is not a real date, yet could still be stored in the integer column or compared to a real date.

In fact even if you used a trigger to make the number eight digits long and also fall within a sensible year range, there are many values that could still be stored that do not represent dates on the calendar. Very strict checking would be required in your application program to ensure such date information was valid.

Similarly, you cannot perform date arithmetic using integer dates. Although 20040101 + 7 gives a date seven days later, 20040330 + 7 would give a number that looks like March 37th.

We have not even looked at a data type to store a time value yet, but the same limitations apply if a numeric field is used. SQLite contains a number of functions that allow you to work with both dates and times stored as character strings, allowing you to manipulate the values in useful ways.

Valid Timestring Formats

SQLite is fairly flexible about the format in which you can specify a date and/or time. The valid time string formats are shown in the following list:

• 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

For the format strings that only specify a time, the date is assumed to be 2000-01-01. Where no time is specified, midday is used. Simply using the string now tells SQLite to use the current date and time.

The format string DDDD.DDDD represents a Julian day numberthe number of days since noon on November 24, 4714 BC, Greenwich Mean Time. SQLite uses Julian date format internally to manipulate date and time values.

Displaying a Formatted Date and Time

The core date and time function in SQLite is strftime(), which has the following prototype:

strftime(format, timestring, modifier, modifier, ...)

This function is based upon the C function strftime() and the format parameter will accept most, although not all, of the same conversion specifiers. The following example shows how a date can be reformatted to MM/DD/YY format using strftime().

sqlite> SELECT strftime('%m/%d/%Y', '2004-10-31');
10/31/2004

Table 3.3 lists the conversions that can be performed by SQLite on a timestring.

Table 3.3. Date and Time Conversion Specifiers

String	Meaning
%d	Day of month, 01-31
%f	Fractional seconds, SS.SSS
%H	Hour, 00-23
%j	Day of year, 001-366
%J	Julian day number, DDDD.DDDD
%m	Month, 00-12
%M	Minute, 00-59
%s	Seconds since 1970-01-01 (unix epoch)
%S	Seconds, 00-59
%w	Day of week, 0-6 (0 is Sunday)
%W	Week of year, 01-53
%Y	Year, YYYY
%%	% symbol

Date and Time Modifiers

Given one or more optional modifier arguments, strftime() can perform a calculation on the date given in timestring.

To add or subtract a period of time, the days, hours, minutes, seconds, months and years modifiers can be used, as shown in these examples:

sqlite> SELECT strftime('%Y-%m-%d', '2004-10-31', '+7 days');
2004-11-07
sqlite> SELECT strftime('%H:%M', '22:00', '+12 hours');
10:00
sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S',
'2004-01-01 00:00:00', '-1 second', '+1 year');
2004-12-31 23:59:59

Note

The modifier keywords can be written as either singular or plural. In the last of the preceding examples, we used 1 second and 1 year rather than 1 seconds and 1 years for readability. SQLite does not understand English grammar, so either is always acceptable.

In these examples we have used the same output format as the original timestring to return the date information in a format that can be recognized by SQLite. You should only format the date differently when you want to display it in your application in a particular way.

To save having to enter the same format strings repeatedly when working with dates, SQLite provides four convenience functions that call strftime() with predefined formats.

Use date() to return a date with the format string %Y-%m-%d and time() to return a time as %H:%S. The function datetime() returns the date and time using these two formats combined. Finally julianday() uses the %J format specifier to return the Julian day number.

The arguments to all four functions are the same as strftime() except that the format argument is omitted. The following example uses datetime() to produce a more concise SQL statement:

sqlite> SELECT datetime('2004-01-01 00:00:00', '-1 second', '+1 year');
2004-12-31 23:59:59

Other modifiers allow you to adjust a date or time to the nearest significant value. Specifying start of month, start of year, or start of day will decrease the value given in timestring to midnight on the first of the month or year, or on that day respectively.

When executed on any day during 2004, the start of year modifier returns 2004-01-01, as shown in the following example:

sqlite> SELECT datetime('now', 'start of year');
2004-01-01 00:00:00

Modifiers are applied to timestring in the order they appear in the statement, as shown in the following example. Note that had the second statement been executed on the last day of the month, the result would have been differentthe start of the following month would have been returned.

sqlite> SELECT datetime('now', 'start of month', '+1 day');
2004-07-02 00:00:00
sqlite> SELECT datetime('now', '+1 day', 'start of month');
2004-07-01 00:00:00

Any number of modifiers can be combined, giving you considerable power when working with dates and times. For instance, the last day of the current month can be found using three modifiers in succession.

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

Handling Different Time Zones

The locale settings of your system will determine which time zone is used when displaying dates and times; however, the underlying system clock will use Coordinated Universal Time (UTC), also known as Greenwich Mean Time (GMT)Greenwich Mean Time (GMT). Your time zone setting will specify a number of hours to be added to or subtracted from the UTC value to arrive at the correct local time.

For instance, to find the local time in New York you have to subtract five hours from UTC, or four hours during daylight savings time. Even in Greenwich, the local time is UTC + 1 hour during the summer months.

To convert between UTC and local time values when formatting a date, use the utc or localtime modifiers. The following examples were run on a system with the timezone set to Eastern Standard Time (UTC 5 hours).

sqlite> SELECT time('12:00', 'localtime');
2000-01-01 07:00:00
sqlite> SELECT time('12:00', 'utc');
2000-01-01 17:00:00