SQLite date and time functions [Notes]

This article is based on the content translation in the official SQLite wiki. If there is anything wrong with the translation, I hope you can point out that, after all, my English skills are really poor. SQLite includes the following five time functions:

1. Date (date and time string, modifier, modifier ,......)
2. Time (date and time string, modifier, modifier ,......)
3. Datetime (date and time string, modifier, modifier ,......)
4. Julianday (date and time string, modifier, modifier ,......)
5. Strftime (date and time format, date and time string, modifier, modifier ,......)

The preceding five functions require a date and time string as the parameter, followed by zero to multiple modifier parameters. The strftime () function also requires a date and time format string as the first parameter.

1. The date () function returns a date in YYYY-MM-DD format;
2. The time () function returns a date time in the format of "YYYY-MM-DD hh: mm: SS;
3. The julianday () function returns the number of days, starting from January 1, November 24, 4714 BC, Greenwich Mean Time;
4. The strftime () function returns a formatted date and time, which can be formatted using the following symbol:
   % D day (December 31, January) 01-31
   % F seconds in decimal form, ss. ssss
   % H hour 00-24
   % J day of the year 01-366
   % J Julian day numbers
   % M month 01-12
   % M minutes 00-59
   % S number of seconds calculated from January 1
   % S seconds 00-59
   % W weeks, 0-6, 0 is Sunday
   % W week of the year 00-53
   % Y year 0000-9999
   % Percent

The other four functions can be represented by the strftime () function:

1. Date (...) -> Strftime ("% Y-% m-% d ",...)
2. Time (...) -> Strftime ("% H: % m: % s ",...)
3. Datetime (...) -> Strftime ("% Y-% m-% d % H: % m: % s ",...)
4. Julianday (...) -> Strftime ("% J ",...)

Date and Time string

You can use the following formats:

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. Dddd. dddd

In the fifth to seventh formats, "T" is a character used to separate the date and time. The eighth to tenth formats only represent the Time of the 2000-01-01 day, 'Now 'in 11th formats indicates that the current date and time are returned, and the Greenwich Mean Time (UTC) is used; the other 12th formats indicate a Julian day numbers.

Modifier

You can use the following modifier to change the date or time:

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 week
10. Start of day
11. Weekday n
12. Unixepoch
13. Localtime
14. UTC

The first six modifiers are the time and date of adding a specified value. The seventh to tenth modifiers indicate that the start of the current date is returned; the first modifier indicates that the next week is the date and time of n. The second modifier indicates the number of seconds since. The second modifier indicates that the local time is returned.

The following are some examples:

1. Current computer time
   Select date ('now ')
2. The last day of the current month of the computer
   Select date ('now ', 'start of month',' + 1 month', '-1 Day ')
3. Calculate the date and time expressed by UNIX timestamp 1092941466
   Select datetime ('20140901', 'unixepoch ')
4. Calculate the local Date and Time in UNIX timestamp 1092941466
   Select datetime ('20140901', 'unixepoch ', 'localtime ')
5. Current UNIX timestamp of the computer
   Select strftime ('% s', 'Now ')
6. How many days do the two dates differ?
   Select jolianday ('now ')-jolianday ('2017-12-23 ')
7. How many seconds is the difference between two date and time?
   Select julianday ('now ') * 86400-julianday ('2017-01-01 02:34:56') * 2004
8. Calculate the date of the first Tuesday of March.
   Select date ('now ', 'start of year',' + 9 months', 'weekday 2 ');