SQLite Date and Time Functions

Summary

The document describes default date and time functions in SQLite. This document is a supplement to the function documentation found on the SQL Expression Syntax page. Function Overview Five date and time functions are available, 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 common 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 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 %% % 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-M...