SQLite strftime() Function

The SQLite strftime() function outputs a specified time value corresponding the specified time value and modifiers in the specified format.

Syntax

Here is the syntax of the SQLite strftime() function:

strftime(format, time_value [, modifier, modifier, ...])

Parameters

format

time_value

Optional. A time value. The time value can be in any of the following formats, as shown below. The value is usually a string, but in the case of format 12 it can be an integer or a floating point number.

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 - the current date and time
12. DDDDDDDDDD.dddddd - Julian days number with fractional part

modifier

Optional. You can use zero or more modifiers to change the time value time_value. Multiple modifiers are applied sequentially from left to right. You can use modifiers like this:

1. NNN days- Add NNN days to the time value
2. NNN hours- Add NNN hours to the time value
3. NNN minutes- Add NNN minutes to the time value
4. NNN.NNNN seconds- Add NNN.NNNN seconds to the time value
5. NNN months- Add NNN months to the time value
6. NNN years- Add NNN years to the time value
7. start of month- Fall back to the beginning of the month in time_value
8. start of year- Fall back to the beginning of the year in time_value
9. start of day- Fall back to the beginning of the day in time_value
10. weekday N- Advance time_value ​​to weekday N
11. unixepoch- Unix timestamp for now
12. julianday- The Julian calendar days for now
13. auto
14. localtime- The current time
15. utc- The utc time

The NNN represents a number. Can be a positive or negative number. If NNN is negative, it means subtraction.

Return value

The SQLite strftime() function returns a string in the specified format. If no arguments are provided, the strftime() function returns the current date.

If you don’t provide the format argument , the strftime() function will return NULL.

All other date or time functions can be implemented using the strftime() function:

• strftime('%Y-%m-%d', ...) is equivalent to date(...).
• strftime('%H:%M:%S', ...) is equivalent to time(...).
• strftime('%Y-%m-%d %H:%M:%S', ...) is equivalent to datetime(...).
• strftime('%J', ...) -- note-1 is equivalent to julianday(...), except that julianday(...) returns a number.
• strftime('%s', ...) -- note-1 is equivalent to unixepoch(...), except that unixepoch(...) returnes an integer.

Examples

Here are some examples to show usages of the SQLite strftime() function.

• Get the current date using the SQLite strftime() function:

  SELECT
      strftime('%Y-%m-%d'),
      strftime('%Y-%m-%d', 'now');
  

  strftime('%Y-%m-%d')  strftime('%Y-%m-%d', 'now')
  --------------------  ---------------------------
  2022-07-26            2022-07-26

• Use SQLite strftime() function to get current datetime:

  SELECT
      strftime('%Y-%m-%d %H:%M:%S'),
      strftime('%Y-%m-%d %H:%M:%S', 'now');
  

  strftime('%Y-%m-%d %H:%M:%S')  strftime('%Y-%m-%d %H:%M:%S', 'now')
  -----------------------------  ------------------------------------
  2022-07-26 08:47:26            2022-07-26 08:47:26

• Get the current time using the SQLite strftime() function:

  SELECT
      strftime('%H:%M:%S'),
      strftime('%H:%M:%S', 'now');
  

  strftime('%H:%M:%S')  strftime('%H:%M:%S', 'now')
  --------------------  ---------------------------
  08:48:40              08:48:40

• Use the SQLite strftime() function to get the Unix timestamp of the current time:

  SELECT
      strftime('%s'),
      strftime('%s', 'now');
  

  strftime('%s')  strftime('%s', 'now')
  --------------  ---------------------
  1658825364      1658825364

• Get the current date using the SQLite strftime() function:

  SELECT strftime('%Y-%m-%d', 'now', 'start of year', '1 year', '-1 day');
  

  strftime('%Y-%m-%d', 'now', 'start of year', '1 year', '-1 d
  ------------------------------------------------------------
  2022-12-31