SQLite time function and time processing

SQLite Paging Display: Select * FROM news ORDER BY id DESC Limit Offset
10
This article is based on the content of the official SQLite WIKI translation, if there is any improper translation of the place want to point out, after all, my English level is really poor. Sqlite
Includes the following five time functions:

Date (datetime string, modifier, modifier, ...)
Time (DateTime string, modifier, modifier,
......)
DateTime (Date time string, modifier, modifier, ...)
Julianday (Date time string, modifier, modifier,
......)
Strftime (Date time format, datetime string, modifier, modifier,
......)
The above five functions require a DateTime string to be used as arguments, followed by 0 to more modifier parameters. and Strftime ()
The function also requires a DateTime format string to do the first argument.

The date () function returns an "YYYY-MM-DD" as the format;
The time () function returns a "YYYY-MM-DD HH:MM:SS"
Date time for the format;
The Julianday () function returns a number of days starting from November 24, 4714 GMT BC;
The strftime () function returns a formatted date and time, which can be formatted with the following symbol for the date and time:
%d January the day of the first 01-31
%f
Seconds in decimal form, SS. SSSS
%H hours 00-24
%j Day of the year 01-366
%J Julian Day
Numbers
%m Month 01-12
%M min 00-59
%s The number of seconds to calculate from the date of 1970-01-01
%s seconds
00-59
%w Week, 0-6,0 is Sunday
%W Week of the year 00-53
%Y year 0000-9999
%% %
Percent Sign
The other four functions can be represented by the strftime () function:

Date (...)
Strftime ("%y-%m-%d",...)
Time (...)
Strftime ("%h:%m:%s",...)
DateTime (...)
Strftime ("%y-%m-%d%h:%m:%s",...)
Julianday (...)
Strftime ("%J",...)
Date Time string

You can use the following formats:

Yyyy-mm-dd
YYYY-MM-DD hh:mm
YYYY-MM-DD HH:MM:SS
Yyyy-mm-dd
HH:MM:SS. Sss
yyyy-mm-ddthh:mm
Yyyy-mm-ddthh:mm:ss
Yyyy-mm-ddthh:mm:ss. Sss
hh:mm
HH:MM:SS
HH:MM:SS. Sss
Now
DDDD. DDDD
The "T" in the fifth to seventh format is a character that splits the date and time, and the eighth to tenth format represents only the time of 2000-01-01, and the 11th format ' Now ' represents the return of a current date and time, using GMT (UTC), and a 12th format that represents a
Julian Day Numbers.

Modifier

Date and time you can change the date or time using the following modifier:

NNN days
NNN hours
NNN minutes
NNN. NNNN seconds
NNN months
NNN
Years
Start of month
Start of year
Start of week
Start of
Day
Weekday
N
Unixepoch
LocalTime
Utc
The first six modifiers simply increase the time and date of the specified value, and the seventh to tenth modifier indicates the start of the current date, and the 11th modifier indicates the date and time that the next one weeks is N, and the 12th modifier indicates the number of seconds since the beginning of 1970-01-01. The 13th modifier indicates the return of local time.

Here are some examples:

Current time of the computer
SELECT date (' Now ')
The last day of the current month of the computer
SELECT date (' Now ', ' start of
Month ', ' +1 month ', '-1 day ')
Calculates the date and time that Unix timestamp 1092941466 represents
SELECT
DateTime (' 1092941466 ', ' Unixepoch ')
Calculates the local date and time represented by the UNIX timestamp 1092941466
SELECT
DateTime (' 1092941466 ', ' Unixepoch ', ' localtime ')
Computer current UNIX Timestamp
SELECT
Strftime ('%s ', ' Now ')
Number of days between two dates
SELECT
Jolianday (' Now ')-jolianday (' 1981-12-23 ')
Two number of seconds between datetime
SELECT
Julianday (' Now ') *86400-julianday (' 2004-01-01
02:34:56 ') *86400
Calculate the date of the first Tuesday of this year October
SELECT date (' Now ', ' start of Year ', ' +9
Months ', ' weekday
2 ');
Get year
Strftime ('%y ', ' 2008-4-28 ')
Get the Month
Strftime ('%m ', ' 2008-4-28 ')
Also, we can get other information through strftime, but remember to quote time

Example 1.
Select DateTime (' Now ');
Result: 2006-10-17 12:55:54

Example 2.
Select DateTime (' 2006-10-17 ');
Result: 2006-10-17 12:00:00

Example 3.
Select DateTime (' 2006-10-17 00:20:00 ', ' +1 hour ', ' 12
Minute ');
Result: 2006-10-17 01:08:00

Example 4.
Select Date (' 2006-10-17 ', ' +1 Day ', ' +1 Year ');
Results: 2007-10-18

Example 5.
Select DateTime (' Now ', ' start of Year ');
Result: 2006-01-01 00:00:00

Example 6.
Select DateTime (' Now ', ' start of Month ');
Result: 2006-10-01 00:00:00

Example 7.
Select DateTime (' Now ', ' start of Day ');
Result: 2006-10-17 00:00:00

Example 8.
Select DateTime (' Now ', ' +10 hour ', ' start of day ', ' +10
Hour ');
Result: 2006-10-17 10:00:00

Example 9.
Select DateTime (' Now ', ' localtime ');
Result: 2006-10-17 21:21:47

Example 10.
Select DateTime (' Now ', ' +8 Hour ');
Result: 2006-10-17 21:24:45

The +1 hour and -12 minute in example 3 indicate that a certain amount of time can be increased or decreased at the base time (the first parameter of the DateTime function).

The start of year in Example 5 represents the time of the beginning of the first day.

As can be seen from Example 8, although the 2nd parameter is added for 10 hours, it is the 3rd parameter "start of
Day "to zero the time to 00:00:00, followed by a 4th parameter at 00:00:00
On the basis of the time increased by 10 hours into the 10:00:00.

Example 9 converts the Greenwich time zone to the cost of the time zone.

Example 10 converts the Greenwich time zone to the East eight zone.

The strftime () function can put YYYY-MM-DD
The date string in the HH:MM:SS format is converted to another form of string.
The syntax for strftime () is strftime (format, date/time, modifier, modifier,
...)

It can be formatted with the following symbols for dates and times:
%d month, 01-31
%f decimal form of the second, SS. Sss
%H hours,
00-23
%j figure out the day of the year, 001-366
%m month, 00-12
%M min, 00-59
%s
Number of seconds from January 1, 1970 to present
%s seconds, 00-59
%w Week, 0-6 (0 is Sunday)
%W calculates the day of the week that the year belongs to,
01-53
%Y years, YYYY
Percent hundred percent semicolon

An example of the use of strftime () is as follows:
Example 11.
Select Strftime ('%y.%m.%d
%h:%m:%s ', ' Now ', ' localtime ');
Result: 2006.10.17 21:41:09

Example 11 using dots as a separation of dates and converting time to local time zone

SQLite time function and time processing