Table of Contents for

Using SQLite

The simplest and most compact representation is the Julian Day. This is a single floating-point value used to count the number of days since noon, Greenwich time, on 24 November 4714 BCE. SQLite uses the proleptic Gregorian calendar for this representation. The Julian value for midnight, 1 January 2010 is 2455197.5. When stored as a 64-bit floating-point value, modern age dates have a precision a tad better than one millisecond.

Many developers have never encountered the Julian Day calendar, but conceptually it is not much different than the more familiar POSIX time() value—it just uses a different value (days, rather than seconds) and a different starting point.

Julian Day values have a relatively compact storage format and are fairly easy to work with. Durations and differences are simple and efficient to calculate, and use the same data representation as points in time. Values are automatically normalized and can be utilize simple mathematic operations. Julian values are also able to express a very broad range of dates, making them useful for historic records. The main disadvantage is that they require conversion before being displayed.

The other popular representation is a formatted text value. These are typically used to hold a date value, a time value, or a combination of both. Although SQLite recognizes a number of formats, most commonly dates are given in the format YYYY-MM-DD, while times are formatted HH:MM:SS, using an hour value of 00 to 23. If a full timestamp is required, these values can be combined. For example, YYYY-MM-DD HH:MM:SS. Although this style of date may not be the most natural representation, these formats are based off the ISO 8601 international standard for representing dates and times. They also have the advantage of sorting chronologically using a simple string sort.

The main advantage of using a text representation is that they are very easy to read. The stored values do not require any kind of translation and can be easily browsed and understood in their native format. You can also pick and choose what parts of the data value are required, storing only a date or only a time of day, making it a bit more clear about the intention of the value. Or, at least, that would be true if it wasn’t for the time zone issue. As we’ll see, times and dates are rarely stored relative to the local time zone, so even text values usually require conversion before being displayed.

The major disadvantage of text values is that any operation (other than display) requires a significant amount of data conversion. Time and date conversions require some complex math, and can make a noticeable impact in the performance of some applications. For example, moving a date one week into the future requires a conversion of the original date into some generalized format, offsetting the value, and converting it back into an appropriate text value. Calculating durations also requires a significant amount of conversion. The conversion cost may not be significant for a simple update or insert, but it can make a very noticeable difference if found in a search conditional.

Text values also require careful normalization of all input values into a standardized format. Many operations, such as sorts and simple comparisons, require that values use the exact same format. Alternate formats can result in equivalent time values being represented by nonequivalent text values. This can lead to inconsistent results from any procedures that operate directly on the text representation. The problem is not just limited to single columns. If a time or date value is used as a key or join column, these operations will only work properly if all of the time and date values use the same format.

For all these concerns, there is still no denying that text values are the easiest to display and debug. While there is significant value in this, make sure you consider the full range of pros and cons of text values (or any other format) before you make a choice.

You may have noticed that none of these formats support a time zone field. SQLite assumes all time and date information is stored in UTC, or Coordinated Universal Time. UTC is essentially Greenwich Mean Time, although there are some minor technical differences.

There are some significant advantages to using UTC time. First and foremost, UTC is unaffected by location. This may seem like a minor thing, but if your database is sitting on a mobile device, it is going to move. Occasionally, it is going to move across time zones. Any displayed time value better shift with the device.

If your database is accessible over the Internet, chances are good it will be accessed from more than one time zone. In short, you can’t ignore the time zone issue, and sooner or later you’re going to have to translate between time zones. Having a universal base format makes this process much easier.

Similarly, UTC is not affected by Daylight Saving Time . There are no shifts, jumps, or repeats of UTC values. DST rules are extremely complex, and can easily differ by location, time of year, or even the year itself, as switch-over times are shifted and moved. DST essentially adds a second, calendar-sensitive time zone to any location, compounding the problems of location and local time conversions. All of these issues can create a considerable number of headaches.

In the end, there are very few justifiable reasons to use anything except UTC. As the name implies, it provides a universal time system that best represents unique moments in time without any context or translation. It might seem silly to convert values to UTC as you input them, and convert them back to local time to display them, but it has the advantage of working correctly, even if the local time zone changes or the DST state changes. Thankfully, SQLite makes all of these conversions simple.

The main utility to manipulate time and date values is the strftime() SQL function:

strftime( format, time, modifier, modifier... )

The strftime() SQL function is modeled after the POSIX strftime() C function. It uses printf() style formatting markers to specify an output string. The first parameter is the format string, which defines the format of the returned text value. The second parameter is a source time value that represents the base input time. This is followed by zero or more modifiers that can be used to shift or translate the input value before it is formatted. Typically all of these parameters are text expressions or text literals, although the time value may be numeric.

In addition to any literal characters, the format string can contain any of the following markers:

For example, the time format HH:MM:SS.sss (including fractional seconds) can be represented by the format string '%H:%M:%f'.

SQLite understands a number of input values. If the format of the time string is not recognized and cannot be decoded, strftime() will return NULL. All of the following input formats will be recognized:

In the case of the second, third, and fourth formats, there is a single literal space character between the date portion and the time portion. The fifth, six, and seventh formats have a literal T between the date and time portions. This format is specified by a number of ISO standards, including the standard format for XML timestamps. The last two formats are assumed to be a Julian Day or (with a modifier) a POSIX time value. These last two don’t require a specific number of digits, and can be passed in as numeric values.

Internally, strftime() will always compute a full timestamp that contains both a date and time value. Any fields that are not specified by the input time string will assume default values. The default hour, minute, and second values are zero, or midnight, while the default date is 1 January 2000.

In addition to doing translations between formats and representations, the strftime() function can also be used to manipulate and modify time values before they are formatted and returned. Zero or more modifiers can be provided:

The first seven modifiers simply add or subtract the specified amount of time. This is done by translating the time and date into a segregated representation and then adding or subtracting the specified value. This can lead to invalid dates, however. For example, applying the modifier '+1 month' to the date '2010-01-31' would result in the date '2010-02-31', which does not exist. To avoid this problem, after each modifier is applied, the date and time values are normalized back to legitimate dates. For example, the hypothetical date '2010-02-31' would become '2010-03-03', since the unnormalized date was three days past the end of February.

The fact that the normalization is done after each modifier is applied means the order of the modifiers can be very important. Careful consideration should be given to how modifiers are applied, or you may encounter some unexpected results. For example, applying the modifier '+1 month' followed by '-1 month' to the date '2010-01-31', will result in the date '2010-02-03', which is three days off from the original value. This is because the first modifier gets normalized to '2010-03-03', which is then moved back to '2010-02-03'. If the modifiers are applied in the opposite order, '-1 month' will convert our starting date to '2009-12-31', and the '+1 month' modifier will then convert the date back to the original starting date of '2010-01-31'. In this instance we end up back at the original date, but that might not always be the case.

The three start of... modifiers shift the current date back in time to the specified point, while the weekday modifier will shift the date forward zero to six days, in order to find a date that falls on the specified day of the week. Acceptable weekday values are 0-6, with Sunday being 0.

The unixepoch modifier can only be used as an initial modifier to a numeric time value. In that case, the value is assumed to represent a POSIX time, rather than a Julian Day, and is translated appropriately. Although the unixepoch modifier must appear as the first modifier, additional modifiers can still be applied.

The last two modifiers are used to translate between UTC and local time representations. The modifier name describes the translation destination, so localtime assumes a UTC input and produces a local output. Conversely, the utc modifier assumes a local time input and produces a UTC output. SQLite is dependent on the local operating system (and its time zone and DST configuration) for these translations. As a result, these modifiers are subject to any errors and bugs that may be present in the time and date libraries of the host operating system.

In an effort to help standardized text formats, avoid errors, and provide a more convenient way to covert dates and times into their most common representations, SQLite has a number convenience functions. Conceptually, these are wrapper functions around strftime() that output the date or time in a fixed format. All four of these functions take the same parameter set, which is essentially the same as the parameters used by strftime(), minus the initial format string.

All four of these functions recognize the same time string and modifier values that strftime() uses.

SQLite recognizes three literal expressions. When an expression that contains one of these literals is evaluated, the literal will take on the appropriate text representation of the current date or time in UTC.

Because these literals return the appropriate value in UTC, an expression such as SELECT CURRENT_TIMESTAMP; may not return the expected result. To get date and time in the local representation, you need to use an expression such as:

SELECT datetime( CURRENT_TIMESTAMP, 'localtime' );

In this case, the literal CURRENT_TIMESTAMP could also be replaced with 'now'.

In some ways, the simplicity of the date and time functions can mask their power. The following examples demonstrate how to accomplish basic tasks.

Here is an example of how to take a local timestamp and store it as a UTC Julian value:

julianday( input_value, 'utc' )

This type of expression might appear in an INSERT statement. To insert the current time, this could be simplified to the 'now' value, which is always given in UTC:

julianday( 'now' )

If you want to display a Julian value out of the database, you’ll want to convert the UTC Julian value to the local time zone and format it. This can be done like this:

datetime( jul_date, 'localtime' )

It might also be appropriate to put an expression like this into a view.

If you wanted to present the date in a format more comfortable for readers from the United States, you might do something like this:

strftime( '%m/%d/%Y', '2010-01-31', 'localtime' );

This will display the date as 01/31/2010. The second parameter could also be a Julian value, or any other recognized date format.

To get the current POSIX time value (which is always in UTC):

strftime( '%s', 'now' )

Or to display the local date and time, given a POSIX time value:

datetime( time_value, 'unixepoch', 'localtime' )

Don’t forget that the input value is usually a simple text value. The value can be built up by concatenating together individual values, if required. For example, the following will calculate a Julian value from individual year, month, and day values that are bound to the statement parameters. Just be sure to bind them as text values with the proper number of leading zeros:

julianday( :year || '-' || :month || '-' || :day )

As you can see, once you understand how to combine different input formats with the correct modifiers, moving back and forth between time representations is fairly easy. This makes it much simpler to store date and time values using native representations that are otherwise unintelligible to most people.