Sqlite Julianday Function

SQLite: julianday Function

This SQLite post explains how to use the SQLite julianday function with syntax and examples.

Description

The SQLite julianday function takes a date, allows you to apply modifiers and then returns the date as a Julian Day. A Julian Day is the number of days since Nov 24, 4714 BC 12:00pm Greenwich time in the Gregorian calendar. The julianday function returns the date as a floating point number.

Syntax

The syntax for the julianday function in SQLite is:

julianday(timestring [, modifier1, modifier2, ... modifier_n ] )

Parameters or Arguments

timestring

A date value. It can be one of the following:

timestring Explanation
now now is a literal used to return the current date
YYYY-MM-DD Date value formatted as 'YYYY-MM-DD'
YYYY-MM-DD HH:MM Date value formatted as 'YYYY-MM-DD HH:MM'
YYYY-MM-DD HH:MM:SS Date value formatted as 'YYYY-MM-DD HH:MM:SS'
YYYY-MM-DD HH:MM:SS.SSS Date value formatted as 'YYYY-MM-DD HH:MM:SS.SSS'
HH:MM Date value formatted as 'HH:MM'
HH:MM:SS Date value formatted as 'HH:MM:SS'
HH:MM:SS.SSS Date value formatted as 'HH:MM:SS.SSS'
YYYY-MM-DDTHH:MM Date value formatted as 'YYYY-MM-DDTHH:MM' where T is a literal character separating the date and time portions
YYYY-MM-DDTHH:MM:SS Date value formatted as 'YYYY-MM-DDTHH:MM:SS' where T is a literal character separating the date and time portions
YYYY-MM-DDTHH:MM:SS.SSS Date value formatted as 'YYYY-MM-DDTHH:MM:SS.SSS' where T is a literal character separating the date and time portions
DDDDDDDDDD Julian date number

modifier1, modifier2, ... modifier_n

Optional. These are modifiers that are applied to the timestring. Each modifier is applied in order and are cumulative. They can be one or more of the following:

modifier Explanation
[+-]NNN years Number of years added/subtracted to the date
[+-]NNN months Number of months added/subtracted to the date
[+-]NNN days Number of days added/subtracted to the date
[+-]NNN hours Number of hours added/subtracted to the date
[+-]NNN minutes Number of minutes added/subtracted to the date
[+-]NNN seconds Number of seconds added/subtracted to the date
[+-]NNN.NNNN seconds Number of seconds (and fractional seconds) added/subtracted to the date
start of year Shifting the date back to the start of the year
start of month Shifting the date back to the start of the month
start of day Shifting the date back to the start of the day
weekday N Moves the date forward to the next date where weekday number is N (0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday)
unixepoch Used with the DDDDDDDDDD timestring to interpret the date as UNIX Time (ie: number of seconds since 1970-01-01)
localtime Adjusts date to localtime, assuming the timestring was expressed in UTC
utc Adjusts date to utc, assuming the timestring was expressed in localtime

Applies To

The julianday function can be used in the following versions of SQLite:

  • SQLite 3.8.6, SQLite 3.8.x, SQLite 3.7.x, SQLite 3.6.x

Example

Let's look at some SQLite julianday function examples and explore how to use the julianday function in SQLite.

Simple Date Example

You could convert a simple date to a Julian Day with the julianday function as follows:

sqlite> SELECT julianday('2016-10-18');
Output: 2457679.5

sqlite> SELECT julianday('2016-10-18 16:45');
Output: 2457680.19791667

sqlite> SELECT julianday('2016-10-18 16:45:30');
Output: 2457680.19826389

Current Date Example

You could retrieve the current date in SQLite using the "now" timestring with the julianday function as follows:

sqlite> SELECT julianday('now');
Output: 2457819.5            (assuming current date is 2017-03-07)

First Day of the Month Example

You could retrieve the first day of the month using the julianday function as follows:

sqlite> SELECT julianday('2017-03-07', 'start of month');
Output: 2457813.5            (equal to 2017-03-01)

sqlite> SELECT julianday('now', 'start of month');
Output: 2457813.5            (assuming current date is 2017-03-07)

sqlite> SELECT julianday('2017-03-07', '-6 days');
Output: 2457813.5

sqlite> SELECT julianday('now', '-6 days');
Output: 2457813.5            (assuming current date is 2017-03-07)

In these examples, we've calculated the first day of the month in a few different ways. The 'start of month' modifier lets us take any date value and calculate the first day of the month for that date. This includes using the 'now' timestring.

We can also add or subtract days to a date value using the 'NNN days' modifier. In our examples, we subtracted 6 days from our date values. Since we had a date value of the 2017-03-07, this returns the first day of the month (ie: 2017-03-01) as a Julian Day which is 2457813.5.

Last Day of the Month Example

You could retrieve the last day of the month using the julianday function as follows:

sqlite> SELECT julianday('2017-03-07', 'start of month', '+1 month', '-1 day');
Output: 2457843.5

sqlite> SELECT julianday('now', 'start of month', '+1 month', '-1 day');
Output: 2457843.5           (assuming current date is 2017-03-07)

sqlite> SELECT julianday('2017-03-07', '+24 days');
Output: 2457843.5

sqlite> SELECT julianday('now', '+24 days');
Output: 2457843.5           (assuming current date is 2017-03-07)

In these examples, we've calculated the last day of the month in a few different ways. First, we use the 'start of month' modifier to calculate the first day of the month and then add 1 month and then subtract 1 day.

We can also add or subtract days to a date value using the 'NNN days' modifier. In our examples, we added 24 days to our date values. Since we had a date value of the 2017-03-07, this returns the last day of the month (ie: 2017-03-31) as a Julian Day which 2457843.5.

Adding/Subtracting Years Example

You can use the julianday function to manipulate a date value and add or subtract years to it. This is done using the 'NNN years' modifier with the julianday function as follows:

sqlite> SELECT julianday('2016-10-16', '+2 years');
Output: 2458407.5          (which is equal to 2018-10-16)

sqlite> SELECT julianday('2012-10-16', '-2 years');
Output: 2455485.5          (which is equal to 2010-10-16)

sqlite> SELECT julianday('now', '+5 years');
Output: 2459645.5          (assuming current date is 2017-03-07)

In these examples, we've used the 'NNN years' modifier to add 2 years in the first example, subtract 2 years in the second example, and add 5 years to the current date in the third example.

Adding/Subtracting Days Example

You can use the julianday function to manipulate a date value and add or subtract days to it. This is done using the 'NNN days' modifier with the julianday function as follows:

sqlite> SELECT julianday('2017-03-07', '+7 days');
Output: 2457826.5          (which is equal to 2017-03-14)

sqlite> SELECT julianday('2017-03-07', '-7 days');
Output: 2457812.5          (which is equal to 2017-02-28)

sqlite> SELECT julianday('now', '+10 days');
Output: 2457839.5          (assuming current date is 2017-03-07)

In these examples, we've used the 'NNN days' modifier to add 7 days in the first example, subtract 7 days in the second example, and add 10 days to the current date in the third example.