SQLite julianday() Function

The SQLite julianday() function converts a time value specified by a time value and modifiers to a Julian days, that is, the number of days since 12:00 PM GMT on November 24, 4714 BC.

Syntax

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

julianday(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 julianday() function returns the number of Julian days (possibly with a fractional part) corresponding to a specified time value. If no arguments are provided, the julianday() function returns the Julian days corresponding to the current datetime.

Examples

Here are some examples to show julianday() common usage of the SQLite function.

  • Use the SQLite julianday() function to get the Julian days of the current time:

    SELECT julianday(), julianday('now');

    julianday()       julianday('now')
----------------  ----------------
2459786.85543663  2459786.85543663

  • Use the SQLite julianday() function to convert 2022-07-26 12:00:00 to Julian days:

    SELECT
    julianday('2022-07-26 12:00:00'),
    julianday('2022-07-26 12:00:00', '-1 day');

    julianday('2022-07-26 12:00:00')  julianday('2022-07-26 12:00:00', '-1 day')
--------------------------------  ------------------------------------------
2459787.0                         2459786.0

  • Use the SQLite julianday() function to get the Julian days number corresponding to the last day of the year:

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

    julianday('now', 'start of year', '1 year', '-1 day')
-----------------------------------------------------
2459944.5