SQLite Date and Time Functions

Sqlite has had date and time functions since about version 2.8.7. The official SQLite Date and Time Functions Documentation is located in the Wiki, meaning it is still in flux.

The information below was copied from the Wiki on Dec 31, 2005, and may be out-of-date by the time you read it. The functions are implemented in the date.c source code.

Functions

The SQLite Tutorial contains a section on how to use the functions. Briefly, the functions are as follows:

   1. date( timestring, modifier, modifier, ...)
        returns date as YYYY-MM-DD
   2. time( timestring, modifier, modifier, ...)
        returns time as HH:MM:SS
   3. datetime( timestring, modifier, modifier, ...)
        returns datetime as YYYY-MM-DD HH:MM:SS
   4. julianday( timestring, modifier, modifier, ...)
        returns julian day, which is a float-point number
        counting the number of days since 4714 B.C.
   5. strftime( format, timestring, modifier, modifier, ...)
        returns a string in the desired format (like printf)
   6. current_time
        returns current time as HH:MM:SS
   7. current_date
        returns current date as YYYY-MM-DD
   8. current_timestamp
        returns current timestamp as YYYY-MM-DD HH:MM:SS

You can specify zero or more modifiers, which allow you to do arithmetic before returning the value. You can use julian day in place of unix timestamps. Note, using parentheses when calling the current_ functions gives an error, oddly.

Time Strings

SQLite understands date/time values entered in the following formats. If the format is invalid, SQLite will not give an error, but the function call will return null.

  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-DDTHH:MM
   6. YYYY-MM-DDTHH:MM:SS
   7. YYYY-MM-DDTHH:MM:SS.SSS
   8. HH:MM
   9. HH:MM:SS
  10. HH:MM:SS.SSS
  11. now
  12. DDDD.DDDD    (julian day as floating-point number)

Modifiers

The following modifiers allow you to add or subtract date or time values. Because each of the functions takes a variable number of modifiers, you can do a chain of arithmetic in one call. When you use a modifer, it must be quoted!

   1. NNN days
   2. NNN hours
   3. NNN minutes
   4. NNN.NNNN seconds
   5. NNN months (see #551 and [1163])
   6. NNN years (see #551 and [1163])
   7. start of month
   8. start of year
   9. start of week (withdrawn -- will not be implemented)
  10. start of day
  11. weekday N (see #551 and [1163])
  12. unixepoch
  13. localtime
  14. utc
  15. julian (not implemented as of 2004-01-05)
  16. gregorian (not implemented as of 2004-01-05) 

Formats

When you want to display a formatted date/time, call strftime with the following escape values in the format string.

   %d  day of month
   %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  sunday==0
   %W  week of year 00-53
   %Y  year 0000-9999
   %%  %