SQLITE Time Field Operation function

Time-date functions in SQLite

This is the note I made when I studied SQLite and translated some of what Chris Newman wrote in "Working with Dates and Times" in "SQLite".

SQLite includes the following time/date functions:
DateTime () ......... ........ Date and time of generation
Date () ........ ............ Date generated
Time () ........ ............ Generation time
Strftime () ........ ......... Format the date and time generated by the above three functions

The usage of datetime () is: datetime (date/time, modifier, modifier ...)
The syntax for date () and time () is the same as DateTime ().

In a time/Date function, you can use a string in the following format as a parameter:
Yyyy-mm-dd
YYYY-MM-DD hh:mm
YYYY-MM-DD HH:MM:SS
Yyyy-mm-dd HH:MM:SS. Sss
hh:mm
HH:MM:SS
HH:MM:SS. Sss
Now
Where now is the time to produce the present.

Example (the time to write this note is October 17, 2006 8 o'clock in the evening to 10, test environment: SQLite 2.8.17,WINXP, Beijing 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 added 10 hours, but was the 3rd parameter "start of day" to zero the time to 00:00:00, followed by the 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 converts date strings in YYYY-MM-DD HH:MM:SS format to other forms of strings.
The syntax of 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 falls within that year, 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 uses a dot as the date separator, and converts the time to the local time zone.

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

Select DateTime (' 2015-06-10 11:17:00 ', ' +1 Day ', '-1 hour ', ' -10 minute ', ' +1 year ', ' start of day ', ' +1 Hour ');

Note that the preceding conditions are overwritten by the following.

Sqlite> Select Strftime ('%y-%m-%d%h:%m:%s ', ' Now ', ' localtime ');

Note that the specified% symbol is used when formatting, which is actually equivalent to the sprintf in C

SQLITE Time Field Operation function