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.
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.
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)
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 ) 6. NNN years (see #551 and ) 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 ) 12. unixepoch 13. localtime 14. utc 15. julian (not implemented as of 2004-01-05) 16. gregorian (not implemented as of 2004-01-05)
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 %% %
Disclaimer: This content is provided as-is. The information may be incorrect.