Commonly used SQL statements of SQLite

 

-- Returns the UTC time.
Select current_timestamp;
-- Returns the local time.
Select datetime (current_timestamp, 'localtime ');
-- Time conversion: yyyy-mm-dd, for example, 2008-08-09
Select datetime ('2014-12-22 ') as [my time];
Select datetime ('2014-12-22 ') as "My time"; -- must be double quotation marks

SelectDate ('now ', 'localtime'); -- returns the local date.

SelectTime ('now ', 'localtime'); -- returns the current local time.

Select current_time; -- returns the current UTC time

Select current_date; -- returns the current UTC date.

Select julianday ('2017-12-22 ')-julianday ('2017-12-21'); -- date comparison

-- Compute the current date.
Select date ('now ');
-- 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 independent.
Select julianday ('now ')-julianday ('2017-07-04 ');

-- Compute the number of seconds since a participant moment in 2004:
Select strftime ('% s', 'Now')-strftime ('% s', '2017-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', 'right') Except t includes des fractional part ):
Select (julianday ('right')-2440587.5) * 86400.0;

Select rowid, * From keys order by rowid ASC limit 5 offset 5; -- SQLite pagination, indicates skipping 5 rows and then taking 5 rows, that is, the second page