Sqlite time functions and Time Processing

Sqlite time function and time processing SQLite page display: Select * FromnewsorderbyiddescLimit10Offset 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 it out, after all, my English skills are poor. SQLite includes the following five time functions: www.2

Sqlite time function and time processing SQLite page display: Select * From news order by id desc Limit 10 Offset this article is based on the content translation in the official SQLite WIKI, if there is anything wrong with translation, I hope you can point out that my English level is really poor after all. SQLite includes the following five time functions: www.2

Sqlite time functions and Time Processing

SQLite pagination: Select * From news order by id desc Limit 10 Offset

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

It includes the following five time functions:

Www.2cto.com

Date (date and time string, modifier, modifier ,......)

Time (date and time string, modifier, modifier,

......)

Datetime (date and time string, modifier, modifier ,......)

Julianday (date and time string, modifier, modifier,

......)

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. Strftime ()

The function also requires a date and time format string as the first parameter.

Www.2cto.com

The date () function returns a date in YYYY-MM-DD format;

The time () function returns a YYYY-MM-DD with HH: MM: SS"

Format: Date and time;

The julianday () function returns the number of days, starting from January 1, November 24, 4714 BC, Greenwich Mean Time;

Strftime ()

The 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 sign

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

Www.2cto.com

Date (...) ->

Strftime ("% Y-% m-% d ",...)

Time (...)

-> Strftime ("% H: % M: % S ",...)

Datetime (...)

-> Strftime ("% Y-% m-% d % H: % M: % S ",...)

Julianday (...)

-> Strftime ("% J ",...)

Date and Time string

You can use 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 format indicates that the current date and time are returned, and the Greenwich Mean Time (UTC) is used. In 12th format, the current date and time are returned.

Julian Day Numbers.

Modifier

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

Www.2cto.com

NNN days

NNN hours

NNN minutes

NNN. NNNN seconds

NNN months

NNN

Years

Start of month

Start of year

Start of week

Start

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:

Www.2cto.com

Current computer time

SELECT date ('now ')

The last day of the current month of the computer

SELECT date ('now ', 'start

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 ') * 86400

Calculate the date of the first Tuesday of March.

SELECT date ('now ', 'start of year',' + 9

Months ', 'weekday

2 ');

Get year

Strftime ('% y', '2017-4-28 ')

Get month

Strftime ('% m', '2017-4-28 ')

Similarly, we can use strftime to obtain other desired information, but remember to enclose the time with quotation marks.

Example 1.

Select datetime ('now ');

Result: 12:55:54

Example 2.

Select datetime ('2017-10-17 ');

Result: 12:00:00

Www.2cto.com

Example 3.

Select datetime ('2017-10-17 00:20:00 ',' + 1 hour ','-12

Minute ');

Result: 01:08:00

Example 4.

Select date ('2014-10-17 ',' + 1 Day', '+ 1 year ');

Result:-10-18

Example 5.

Select datetime ('now ', 'start of year ');

Result: 00:00:00

Example 6.

Select datetime ('now ', 'start of month ');

Result: 00:00:00

Www.2cto.com

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

Example 9.

Select datetime ('now ', 'localtime ');

Result: 21:21:47

Example 10.

Select datetime ('now ',' + 8 hour ');

Result: 21:24:45

In Example 3, + 1 hour and-12 minute indicate that the time can be increased or decreased in the Basic time (the first parameter of the datetime function.

In Example 5, start of year indicates the start time of the year.

Www.2cto.com

As shown in Example 8, although 2nd parameters are added for 10 hours

Day "returns the time To 00:00:00, and the subsequent 4th parameters are 00:00:00

The time is increased by 10 hours to 10:00:00.

In Example 9, the Greenwich Mean Time Zone is converted to the local time zone.

In Example 10, the Greenwich Mean Time Zone is converted to UTC + 8.

The strftime () function can take YYYY-MM-DD

HH: MM: Date string in SS format is converted to another string.

Strftime () syntax is strftime (format, date/time, modifier, modifier,

...)

Www.2cto.com

It can be formatted with 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

The number of seconds from January 1, January 1, 1970 to the present.

% S seconds, 00-59

% W weeks, 0-6 (0 is Sunday)

% W calculates the week of the year for a day,

01-53

% Y, YYYY

% Percent sign

Www.2cto.com

The usage 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 use Dots as the separator of dates and convert the time to the local time zone