Date and Time Functions of SQLite

From http://it.enorth.com.cn/system/2008/06/17/003409310.shtml

This articleArticleAccording to the content translation in the official SQLite wiki, 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. It 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, which can be in the following formats:

YYYY-MM-DD

YYYY-MM-DD hh: Mm.

YYYY-MM-DD hh: mm: Ss.

YYYY-MM-DD hh: mm: Ss. Sss.

YYYY-MM-DDTHH: mm

YYYY-MM-DDTHH: mm: SS

YYYY-MM-DDTHH: mm: Ss. Sss

Hh: mm

Hh: mm: SS

Hh: mm: Ss. Sss

Now

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.

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

Nnn days

Nnn hours

Nnn minutes

Nnn. NNNN seconds

Nnn months

Nnn years

Start of month

Start of year

Start of week

Start of day

Weekday n

Unixepoch

Localtime

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:

Current computer time

Select date ('now ')

The last day of the current month of the computer

Select date ('now ', 'start of month',' + 1 month', '-1 Day ')

Calculate the date and time expressed by UNIX timestamp 1092941466

Select datetime ('20140901', 'unixepoch ')

Calculate the local Date and Time in UNIX timestamp 1092941466

Select datetime ('20140901', 'unixepoch ', 'localtime ')

Current UNIX timestamp of the computer

Select strftime ('% s', 'Now ')

How many days do the two dates differ?

Select jolianday ('now ')-jolianday ('2017-12-23 ')

How many seconds is the difference between two date and time?

Select julianday ('now ') * 86400-julianday ('2017-01-01 02:34:56') * 2004

Calculate the date of the first Tuesday of March.

Select date ('now ', 'start of year',' + 9 months', 'weekday 2 ');