SQLite Julianday() Function

Here we will learn SQLite julianday() function with examples and how to use SQLite julianday() function to get julian day of current year with examples.

SQLIte JulianDay() Function

In SQLite julianday() function is used to get Julian day of year.

Syntax of SQLite julianday() Function

Following is the syntax of using SQLite julianday() function to get julian day of year.

 

julianday(datetimestring, [modifier1, modifier2…, modifierN])

Here the SQLite julianday() function takes datetimestring and one or more modifier values to return Julian day.

 

In above SQLite date() syntax the 1st argument datetimestring will accept the values in following formats.

 

FormatExample
now 2016-08-30
YYYY-MM-DD 2016-08-30
YYYY-MM-DD HH:MM 2016-08-30 18:47
YYYY-MM-DD HH:MM:SS 2016-08-30 18:47:56
YYYY-MM-DD HH:MM:SS.sss 2016-08-30 18:47:56.235
YYYY-MM-DDTHH:MM T literal is used to separate date & time
YYYY-MM-DDTHH:MM:SS T literal is used to separate date & time
YYYY-MM-DDTHH:MM:SS.sss T literal is used to separate date & time
HH:MM 18:50
HH:MM:SS 06:52:25
HH:MM:SS.sss 06:26:56.123
DDDDDDD Julian date number
DDDDDDD.ddddddd Julian date number with fractional part

The 2nd parameter modifiers (modifier1, modifier2, … etc.) will accept the values in following format

 

FormatExample
XX days 02 days
XX hours 10 hours
XX minutes 15 minutes
XX.XXXX seconds 10.15 seconds
XX months 04 months
XX years 02 years
start of month start of month
start of year start of year
start of day start of day
weekday X weekday 2
unixepoch unixepoch
localtime localtime
utc utc

Examples of SQLite julianday() Function

We will see how to use SQLite julianday() function to get Julian day of year with examples.

 

Following is the sqlite statement to return days since you born. Here we are considering date 28th December 1992.

 

sqlite> SELECT julianday('now') - julianday('1992-12-28') as datedif;

 

datedif

------------------

8647.40788136562

The following sqlite statement will return a number of days since the day of Independence of India.

 

sqlite> SELECT julianday('now') - julianday('1947-08-15') as datediff;

 

datediff

------------------------------------------

25219.40896978

Now let’s look at the example of julianday() function with table employee. Consider the employee table has the following records.

 

eid         ename       joining_date  salary      Appraisal date

----------  ----------  ------------  ----------  --------------

1           Rati        2016-08-01    5200.0      2017-02-01

2           Umang       2016-01-01    5700.0      2016-07-01

3           Prachi      2015-06-01    5500.0      2015-12-01

4           Ashmite     2015-11-30    7000.0      2016-05-30

Following is the sqlite statement to get from how long employees are working in the organization.

 

SELECT eid, ename, joining_date, salary, julianday('now') - julianday(joining_date) as Workdays FROM Employee;

 

eid         ename       joining_date  salary      Workdays

----------  ----------  ------------  ----------  ----------------

1           Rati        2016-08-01    5200.0      30.4127106829546

2           Umang       2016-01-01    5700.0      243.412710682955

3           Prachi      2015-06-01    5500.0      457.412710682955

4           Ashmite     2015-11-30    7000.0      275.412710682955

This is how SQLite julian day can be used in queries to get julian day of the year based on our requirements. 

Previous
 
Next