Brief introduction to the use of SQLite time function and summary analysis
- 2020-05-17 06:46:33
- OfStack
This paper mainly explains the analysis and summary of time function in SQLite and gives use cases. The examples presented in this article have been tested.
SQLite time/date function type:
1. datetime() : date and time of generation
2. date() : date of production
3: time() : generation time
4. strftime() : format the date and time generated by the above three functions
SQLite time/date function usage:
1. The usage of datetime() is: datetime(date/time, modifier, modifier...)
2. date() and time() have the same syntax as datetime().
3. The strftime() function converts the YYYY-MM-DD HH:MM:SS date string into a string of other forms.
The syntax for strftime() is strftime(format, date/time, modifier, modifier,...)
SQLite time/date function format:
datetime(), date(), and time() can take the following strings as arguments in the time/date functions:
1, MM YYYY - - 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 where now is the time of creation of the present.
strftime() can format dates and times with the following symbols:
%d month, 01-31
%f decimal seconds, SS.SSS
%H hours, 00-23
%j calculates that a given day is the day of the year, 001-366
%m month, 00-12
%M minutes, 00-59
%s seconds from January 1, 1970 to the present
% S seconds, 00-59
%w week, 0-6 (0 is Sunday)
%W calculates which week of the year a particular day belongs to, 01-53
% Y years, YYYY
% % percent
SQLite time/date function use case:
Case 1. select datetime (' now '); Result: 2012-05-16 03:23:21
Case 2. select datetime (' 2012-05-16 '); Result: 2012-05-16 00:00:00
Example 3. select datetime('2012-05-16 00:20:00','3 hour','-12 minute'); Results: 2012-05-16 03:08:00
Note: 3 hour and -12 minute indicate that the base time (the first parameter of the datetime function) can be increased or decreased by 1 definite time.
Example 4. select date('2012-05-16','1 day','1 year'); Results: the 2013-05-17
Example 5. select datetime('now','start of year'); Result: 2012-01-01 00:00:00
Note: start of year means the beginning of a year.
Example 6. select datetime('now','start of month'); Result: 2012-05-01 00:00:00
Example 7. select datetime('now','start of day'); Result: 2012-05-16 00:00:00
Example 8. select datetime('now','10 hour','start of day','10 hour'); Results: 2012-05-16 10:00:00
Note: although 10 hours were added to the second parameter, the time was reset to 00:00:00 by the third parameter "start of day", and then the time increased by 10 hours to 10:00:00 based on 00:00:00 by the fourth parameter.
Example 9. select datetime (' now ', 'localtime'); Result: datetime('now','localtime') 2012-05-16 11:52:20
Description: greenway time zone conversion cost time zone
Example 10. select datetime('now','8 hour'); Result: 2012-05-16 11:53:41
Note: convert Greenwich time zone to east zone 8.