Sqflite Date

 

Available Methods:

1. date(time-value, modifier, modifier, ...)
2. time(time-value, modifier, modifier, ...)
3. datetime(time-value, modifier, modifier, ...)
4. julianday(time-value, modifier, modifier, ...)
5. strftime(format, time-value, modifier, modifier, ...)

strftime() substitutions:

               %d day of month: 00

%f fractional seconds: SS.SSS

%H hour: 00-24

%j day of year: 001-366

%J Julian day number

%m month: 01-12

%M minute: 00-59

%s seconds since 1970-01-01

%S seconds: 00-59

%w day of week 0-6 with Sunday==0

%W week of year: 00-53

%Y year: 0000-9999

%% %

Modifiers:

1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10. weekday N
11. unixepoch
12. localtime
13. utc

    Examples:

Compute the current date.

SELECT date('now');

                        //  2021-12-24

Compute the last day of the current month.

SELECT date('now','start of month','+1 month','-1 day');

Compute the date and time given a unix timestamp 1092941466.

SELECT datetime(1092941466, 'unixepoch');

Compute the date and time given a unix timestamp 1092941466, and compensate for your local timezone.

SELECT datetime(1092941466, 'unixepoch', 'localtime');

Compute the current unix timestamp.

SELECT strftime('%s','now');

Compute the number of days since the signing of the US Declaration of Independence.

SELECT julianday('now') - julianday('1776-07-04');

Compute the number of seconds since a particular moment in 2004:

SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');

Compute the date of the first Tuesday in October for the current year.

SELECT date('now','start of year','+9 months','weekday 2');

Compute the time since the unix epoch in seconds (like strftime('%s','now') except includes fractional part):

SELECT (julianday('now') - 2440587.5)*86400.0;

More Practical Example With Employee Table

 CREATE TABLE Employee

(eid INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT,

joining_date TEXT,

salary REAL);

INSERT INTO Employee(ename,joining_date,salary)

VALUES ('Rati',date('now','start of month'),5200),

('Umang',date('now','start of year'),5700),

('Prachi',date('now','start of year','-7 months'),5500),

('Ashmite',date('now','start of year','-32 days'),7000);

SELECT * FROM Employee;

eid         ename       joining_date  salary

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

1           Rati        2016-08-01    5200.0

2           Umang       2016-01-01    5700.0

3           Prachi      2015-06-01    5500.0

4           Ashmite     2015-11-30    7000.0

// add 6 month from joining

SELECT eid, ename, joining_date, salary, date(joining_date,'6 months') as 'Appraisal date' FROM Employee;

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

Get the employee Data Joined in last 30 days

SELECT eid, ename, joining_date, salary FROM Employee 

WHERE joining_date BETWEEN date('now', '-30 days') AND date('now');

eid    ename    joining_date      salary

1 Rati 2021-12-01 5200.0

Thank You