Sqlite time functions and time processing in iPhone applications

IPhoneApplicationSqlite timeFunctions andTimeProcessing is the content to be introduced in this article.Sqlite timeFunctions andTimeThe processing method. Let's look at the details. This article is based onSQLiteIf there is anything wrong with the content translation in the official WIKI, I hope you can point out that, after all, my English skills are really poor.SQLiteIncludes the following fiveTimeFunction:

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. The strftime () function also requires a date and time format string as the first parameter.

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

The time () function returns a date time in the format of "YYYY-MM-DD HH: MM: SS;

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

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:

 
 

  
  
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, 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.

Modifier

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(‘1092941466’,’unixepoch’) 
 
 

Calculate the local Date and Time in UNIX timestamp 1092941466

 
 

  
  
SELECT datetime(‘1092941466’,’unixepoch’,’localtime’) 
 
 

Current UNIX timestamp of the computer

 
 

  
  
SELECT strftime(‘%s’,’now’) 
 
 

How many days do the two dates differ?

 
 

  
  
SELECT jolianday(‘now’)-jolianday(‘1981-12-23’) 
 
 

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

 
 

  
  
SELECT julianday('now')*86400 - julianday('2004-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’,'2008-4-28') 
 
 

Get month

 
 

  
  
strftime(‘%m’,'2008-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
  
  
 
  
  
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
  
  
 
  
  
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 first parameter of the datetime function can be added or removed for a certain period of time.

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

As shown in Example 8, although 2nd parameters are added for 10 hours, the time is reset to 00:00:00 by the 3rd parameter "start of day", and the subsequent 4th parameters are set at 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 converts a 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 ,...)

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

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
 
 

In Example 11, the time is separated by dots and converted to the time in the local time zone.

Summary:IPhoneApplicationSqlite timeThe function and time processing content have been introduced. I hope this article will help you!