SQLite unixepoch() Function

The SQLite unixepoch() function converts a time value specified by a time value and modifiers to a Unix timestamp, that is, the number of seconds from UTC 1970-01-01 00:00:00.

Syntax

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

unixepoch(time_value [, modifier, modifier, ...])

Parameters

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 unixepoch() function returns a Unix timestamp corresponding to a specified time value. If no arguments are provided, the unixepoch() function returns the Unix timestamp corresponding to the current datetime.

Examples

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

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

    SELECT unixepoch(), unixepoch('now');

    unixepoch()  unixepoch('now')
-----------  ----------------
1658823494   1658823494

  • Use the SQLite unixepoch() function to convert 2022-07-26 12:00:00 to a Unix timestamp:

    SELECT
    unixepoch('2022-07-26 12:00:00'),
    unixepoch('2022-07-26 12:00:00', '-1 second');

    unixepoch('2022-07-26 12:00:00')  unixepoch('2022-07-26 12:00:00', '-1 second')
--------------------------------  ---------------------------------------------
1658836800                        1658836799

  • Use the SQLite unixepoch() function to get the Unix timestamp corresponding to the last day of the current year:

    SELECT unixepoch('now', 'start of year', '1 year', '-1 day');

    unixepoch('now', 'start of year', '1 year', '-1 day')
-----------------------------------------------------
1672444800