Usage instructions and summary analysis of SQLite Time Functions

Source: Internet
Author: User

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
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.