Usage instructions and summary analysis of SQLite Time Functions

This article mainly describes the analysis and summary of time functions in SQLite and provides use cases. The examples given in this article are all tested.
SQLite time/date function types:
1. datetime (): Date and time of Generation
2. date (): date of Generation
3:, time (): generation time
4. strftime (): format the date and time generated by the preceding three functions.

SQLite time/date function usage:
1. The usage of datetime () is: datetime (date/time, modifier, modifier ...)
2. the syntax of date () and time () is the same as that of datetime.
3. The strftime () function can convert the date string in the YYYY-MM-DD HH: MM: SS format to another form of string.
Strftime () syntax is strftime (format, date/time, modifier, modifier ,...)

Format SQLite time/date functions:
Datetime (), date (), and time () functions can use strings in the following format as parameters:
1. YYYY-MM-DD
2. YYYY-MM-DD HH: MM
3. YYYY-MM-DD HH: MM: SS.
4. YYYY-MM-DD HH: MM: SS. SSS
5. HH: MM
6. HH: MM: SS
7. HH: MM: SS. SSS
8. now indicates the current generation time.

Strftime () can be formatted using the following symbols:
% D month, 01-31
% F seconds in decimal form, SS. SSS
% H hour, 00-23
% J calculates the day of the year, 001-366
% M month, 00-12
% M minutes, 00-59
% S seconds from January 1, January 1, 1970 to the present
% S seconds, 00-59
% W weeks, 0-6 (0 is Sunday)
% W calculate the week of the year for a day, 01-53
% Y, YYYY
% Percent sign

SQLite time/date functions:

Example 1. select datetime ('now '); Result: 03:23:21

Example 2. select datetime ('2017-05-16 ');Result: 00:00:00

Example 3. select datetime ('2017-05-16 00:20:00 ', '3 hour','-12 minute '); Result: 2012 03:08:00
Note: 3 hour and-12 minute indicate that the time can be increased or decreased in the Basic time (the first parameter of the datetime function.

Example 4: select date ('1970-05-16 ', '1 Day', '1 year'); Result: 2012

Example 5: select datetime ('now ', 'start of year'); Result: 00:00:00
Note: start of year indicates the start time of a year.

Example 6: select datetime ('now ', 'start of month'); Result: 00:00:00

Example 7: select datetime ('now ', 'start of Day'); Result: 00:00:00

Example 8: select datetime ('now ', '10 hour', 'start of Day', '10 hour '); Result: 10:00:00
Note: Although 2nd parameters are added for 10 hours, the time is reset to 00:00:00 by the 3rd "start of day" parameter, the subsequent 4th parameters increased the time by 10 hours to 00:00:00 on the basis of 10:00:00.

Example 9. select datetime ('now ', 'localtime'); Result: datetime ('now', 'localtime') 11:52:20
Note: Greenwich Mean Time Zone conversion cost local time zone

Example 10. select datetime ('now ', '8 hour ');Result: 11:53:41
Description: converts Greenwich Mean Time Zone to UTC + 8.

Example 11. select strftime ('% Y. % m. % d % H: % M: % s', 'Now', 'localtime'); Result: 11:59:56