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.
In SQLite julianday() function is used to get Julian day of year.
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.
Format | Example |
---|---|
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
Format | Example |
---|---|
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 |
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.