SQLite Date and Time Functions

Document directory

• Time string)

SQLite supports the following five date and time functions:

1. Date (Timestring, modifier, modifier ,...)
2. Time (Timestring, modifier, modifier ,...)
3. Datetime (Timestring, modifier, modifier ,...)
4. Julianday (Timestring, modifier, modifier ,...)
5. Strftime (Format, timestring, modifier, modifier ,...)

All these five functions are based on timestring. At the same time, zero or multiple modifiers can be received after timestring. In addition, the strftime () function is a little different from the other four functions. It can also receive a character escape format (which is used by the printf function in the C function ).

The date-time function of SQLite uses a subset of the ISO-8601 date-time format specification. Date () function returns the Date Format: YYYY-MM-DD, time () function returns the time format: hh: mm: SS, datetime () function returns the format of "YYYY-MM-DD hh: mm: SS ". The return value of julianday () is Julian day, from noon on January 1, November 24, 4714 BC (based on Greenwich Mean Time) to the current day. The strftime () function is used to format the user's time input (the second parameter) according to the format specified by the user (the first parameter ). The composition of the format character escape is similar to that of the format parameter in the C function, but the meaning is different. The main features are as follows:
% D day of month: 00
% F fractional seconds: Ss. Sss
% H hour: 00-24
% J day of Year: 001 to 366
% J Julian day number
% M month: 01-12
% M minute: 00-59
% S seconds since 1970-01-01
% S seconds: 00-59
% W day of week 0-6 with Sunday = 0
% W week of Year: 00-53
% Y year: 0000-9999
%

In addition, the strftime () function can be used for all formats that can be expressed by other date and time functions, as shown below:
FunctionEquivalent strftime ()
Date (...) Strftime ('% Y-% m-% d ',...)
Time (...) Strftime ('% H: % m: % s ',...)
Datetime (...) Strftime ('% Y-% m-% d % H: % m: % s ',...)
Julianday (...) Strftime ('% J ',...)

However, the reason why other functions except strftime are provided is to consider both convenience and performance.

Time string)

Time escape can be any of the following forms:

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. YYYY-MM-DDTHh: mm
6. YYYY-MM-DDTHh: mm: SS
7. YYYY-MM-DDTHh: mm: Ss. Sss
8. Hh: mm
9. Hh: mm: SS
10. Hh: mm: Ss. Sss
11. Now
12. Dddddddddd

In 5th to 7, t is used to separate the date time (refer to the ISO-8601 ). From 8 to 10, the time format is specified. Because there is no date input, the default dates of these formats are 2000-01-01. In 11th, 'Now 'is converted to the current date and time.

Modifiers)

The time string background can contain 0 or more modifiers to help you change the time string. Each modifier is a conversion of its left value. When there are multiple modifiers, the order in which they take effect is from left to right. Available modifiers include:

1. Nnn days
2. Nnn hours
3. Nnn minutes
4. Nnn. NNNN seconds
5. Nnn months
6. Nnn years
7. Start of month
8. Start of year
9. Start of day
10. Weekday n
11. Unixepoch
12. Localtime
13. UTC

The first six modifiers increase or decrease the time result after the time escape and the previous modifier are processed. For example, for the time in YYYY-MM-DD format, when the "± NNN months" modifier is used, the corresponding number of months is increased/decreased for mm.

Example

Current date
Select date ('now ');

Last day of the month
Select date ('now ', 'start of month',' + 1 month', '-1 Day ');

Convert UNIX timestamp to time Date Format
Select datetime (1092941466, 'unixepoch ');

Convert UNIX timestamp to local time
Select datetime (1092941466, 'unixepoch', 'localtime ');

UNIX timestamp format of the current date
Select strftime ('% s', 'Now ');

Calculate the date difference between the current day and the U.S. Independence Day (in days)
Select julianday ('now ')-julianday ('2017-07-04 ′);

Calculate the time difference from any time to the current time (in seconds)
Select strftime ('% s', 'Now')-strftime ('% s', '2017-01-01 02:34:56 ′);

Set the date to one Tuesday in January 1, October of this year
Select date ('now ', 'start of year',' + 9 months', 'weekday 2 ′);

Original: http://blog.romebuilder.com /? P = 96