SQLite date Type "Go"

SQLite Date Type

Simple example:

SELECT
DateTime (change_date, ' localtime '),
Strftime ('%y-%m-%d ', change_date, ' localtime '),
DateTime (' Now ', ' localtime '),
Strftime ('%y-%m-%d ', ' Now ', ' localtime '),
DATE (' Now ', ' localtime '),
Time (' Now ', ' localtime '),
Time (' 2010-11-27 01:12:21 ', ' localtime ', '-8 hour ') as time
From Salary_history;

SELECT * from salary_history WHERE date (change_date, ' localtime ') =date (' Now ', ' localtime ')

Data types supported by Sqlite3
Null
INTEGER
REAL
TEXT
Blob
But in fact, Sqlite3 also accepts the following data types:
smallint a 16-bit integer.
Interger a 32-bit integer.
Decimal (P,S) p exact value and S-Size decimal integer, the exact value p is the total number (digits) size value, s refers to the number of digits after the decimal point. If there is no special designation, the system will be set to p=5; S=0.
Float 32-bit real number.
A double 64-bit real number.
char (n) n-length string, n cannot exceed 254.
The varchar (n) length is not fixed and its maximum length is n, and N cannot exceed 4000.
Graphic (n) is the same as char (n), but its unit is two characters Double-bytes and N cannot exceed 127. This pattern is designed to support two character-length fonts, such as Chinese characters.
Vargraphic (n) variable-length double-character string with a maximum length of n, N cannot exceed 2000
Date contains the year, month, and date.
Time contains hours, minutes, seconds.
Timestamp contains the year, month, day, time, minute, second, 1 per thousand seconds.

SQLite includes the following time/date functions:
DateTime () ......... ........ Date and time of generation
Date () ........ ............ Date generated
Time () ........ ............ Generation time
Strftime () ........ ......... Format the date and time generated by the above three functions

The usage of datetime () is: datetime (date/time, modifier, modifier ...)
The syntax for date () and time () is the same as DateTime ().

In a time/Date function, you can use a string in the following format as a parameter:
Yyyy-mm-dd
YYYY-MM-DD hh:mm
YYYY-MM-DD HH:MM:SS
Yyyy-mm-dd HH:MM:SS. Sss
hh:mm
HH:MM:SS
HH:MM:SS. Sss
Now
Where now is the time to produce the present.

Example (the time to write this note is October 17, 2006 8 o'clock in the evening to 10, test environment: SQLite 2.8.17,WINXP, Beijing Time):

Example 1.
Select DateTime (' Now ');
Result: 2006-10-17 12:55:54

Example 2.
Select DateTime (' 2006-10-17 ');
Result: 2006-10-17 12:00:00

Example 3.
Select DateTime (' 2006-10-17 00:20:00 ', ' +1 hour ', ' -12 minute ');
Result: 2006-10-17 01:08:00

Example 4.
Select Date (' 2006-10-17 ', ' +1 Day ', ' +1 Year ');
Results: 2007-10-18

Example 5.
Select DateTime (' Now ', ' start of Year ');
Result: 2006-01-01 00:00:00

Example 6.
Select DateTime (' Now ', ' start of Month ');
Result: 2006-10-01 00:00:00

Example 7.
Select DateTime (' Now ', ' start of Day ');
Result: 2006-10-17 00:00:00

Example 8.
Select DateTime (' Now ', ' +10 hour ', ' start of day ', ' +10 Hour ');
Result: 2006-10-17 10:00:00

Example 9.
Select DateTime (' Now ', ' localtime ');
Result: 2006-10-17 21:21:47

Example 10.
Select DateTime (' Now ', ' +8 Hour ');
Result: 2006-10-17 21:24:45

The +1 hour and -12 minute in example 3 indicate that a certain amount of time can be increased or decreased at the base time (the first parameter of the DateTime function).

The start of year in Example 5 represents the time of the beginning of the first day.

As can be seen from Example 8, although the 2nd parameter added 10 hours, but was the 3rd parameter "start of day" to zero the time to 00:00:00, followed by the 4th parameter at 00:00:00
On the basis of the time increased by 10 hours into the 10:00:00.

Example 9 converts the Greenwich time zone to the cost of the time zone.

Example 10 converts the Greenwich time zone to the East eight zone.

The strftime () function converts date strings in YYYY-MM-DD HH:MM:SS format to other forms of strings.
The syntax of Strftime () is strftime (format, date/time, modifier, modifier, ...)

It can be formatted with the following symbols for dates and times:
%d month, 01-31
%f decimal form of the second, SS. Sss
%H hours, 00-23
%j figure out the day of the year, 001-366
%m month, 00-12
%M min, 00-59
%s number of seconds from January 1, 1970 to present
%s seconds, 00-59
%w Week, 0-6 (0 is Sunday)
%W calculates the day of the week that falls within that year, 01-53
%Y years, YYYY
Percent hundred percent semicolon

An example of the use of strftime () is as follows:

Example 11.
Select Strftime ('%y.%m.%d%h:%m:%s ', ' Now ', ' localtime ');
Result: 2006.10.17 21:41:09

Letter of the article:

Arithmetic function  
ABS (X) returns the absolute value of a given numeric expression.  
Max (x,y[,...]) returns the maximum value of an expression.  
min (x,y[,...]) returns the minimum value of an expression.  
Random (*) returns the stochastic number.  
Round (X[,y]) returns a numeric expression and rounds it to the specified length or precision.  
character processing function  
Length (X) returns the number of characters for a given string expression.  
Lower (X) returns a character expression after converting the uppercase character data to lowercase characters.  
Upper (X) returns a character expression that converts lowercase character data to uppercase.  
substr (x, y, z) returns part of an expression.  
Randstr ()  
Quote (A)  
Like (A, B) determines whether the given string matches the specified pattern.  
Glob (A, b)  
Conditional judgment function  
COALESCE (x,y[,...])  
Ifnull (x, y)  
Nullif (x, y)  
aggregate function  
avg (X) returns the average of the values in the group.  
Count (X) returns the number of items in the group.  
Max (X) returns the maximum value of a value in a group.  
min (X) returns the minimum value of a value in a group.  
sum (X) returns the sum of all values in an expression.  
other functions  
typeof (X) returns the type of data.  
Last_insert_rowid () returns the ID of the last inserted data.  
Sqlite_version (*) returns the version of SQLite.  
Change_count () returns the number of rows affected by the previous statement.  
Last_statement_change_count ()

Management tools: http://www.sqlitedeveloper.com/
sqlite:http://sqlite.phxsoftware.com/

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/fer_ba/archive/2009/09/23/4582205.aspx

For example today: 200-02-22 
The result of running the following statement is different:  
Select Date (' Now ')  
Result: 2009-02-21 
Select DateTime (' Now ', ' localtime ')  
Result: 2009-02-22 00:52:04 
---------------------------Separator Line------------ -------------- 
So the second statement is used to obtain the correct result, for example (take a record greater than the current time):  
select * from table where date field >datetime (' Now ', ' LocalTime ')  
As if there is no datediff such as MSSQL function  
The following statement implements the same number of data for the DateDiff (' m ', start date, end Date ') function:  
SELECT * From table where strftime ('%m ', date field) =strftime ('%m ', ' Now ')  
So you can achieve a comparison of two dates, extrapolate, also use the strftime format date to compare days, weeks, and years  
Refer to SQLite Date function detail document:  
http://www.xueit.com/html/2009-02/27_649_00.html 
--------------- ------------dividers are collected in the network------------- 
Select * from placard where Placard_endtime > DateTime (' Now ');

SELECT placard_starttime,strftime ('%s ', placard_starttime), datetime (' Now '), Strftime ('%s ', ' Now ') from placard
8 Time Zone Difference ...
SELECT placard_starttime,strftime ('%s ', placard_starttime), datetime (' Now ', ' localtime '), Strftime ('%s ', ' Now ', ' LocalTime ') from placard

Previous: The first one that is greater than the specified time
Select Title,pubtime from article where pubtime> ' 2008-06-15 03:35:28 ' ORDER by pubtime ASC Limit 1 Offset 0

Looks like time format has strict requirements 2008-06-15 03:35:28 can only be used in front of-only: less than two digits of the complement 0

Next: The first bar less than the specified time
Select Title,pubtime from article where pubtime< ' 2008-06-15 03:35:28 ' ORDER by pubtime Desc Limit 1 Offset 0

Notice the accuracy of the time. There's no after 03:35:28.
03:35:28.000

Article to self-study IT network: http://www.xueit.com/html/2009-02/27_647_00.html

This is the note I made when I studied SQLite and translated some of what Chris Newman wrote in "Working with Dates and Times" in "SQLite".
SQLite includes the following time/date functions:
DateTime () ......... ........ Date and time of generation
Date () ........ ............ Date generated
Time () ........ ............ Generation time
Strftime () ........ ......... Format the date and time generated by the above three functions

The usage of datetime () is: datetime (date/time, modifier, modifier ...)
The syntax for date () and time () is the same as DateTime ().
In a time/Date function, you can use a string in the following format as a parameter:
Yyyy-mm-dd
YYYY-MM-DD hh:mm
YYYY-MM-DD HH:MM:SS
Yyyy-mm-dd HH:MM:SS. Sss
hh:mm
HH:MM:SS
HH:MM:SS. Sss
Now
Where now is the time to produce the present.
Example (the time to write this note is October 17, 2006 8 o'clock in the evening to 10, test environment: SQLite 2.8.17,WINXP, Beijing Time):
Example 1.
Select DateTime (' Now ');
Result: 2006-10-17 12:55:54
Example 2.
Select DateTime (' 2006-10-17 ');
Result: 2006-10-17 12:00:00
Example 3.
Select DateTime (' 2006-10-17 00:20:00 ', ' +1 hour ', ' -12 minute ');
Result: 2006-10-17 01:08:00
Example 4.
Select Date (' 2006-10-17 ', ' +1 Day ', ' +1 Year ');
Results: 2007-10-18
Example 5.
Select DateTime (' Now ', ' start of Year ');
Result: 2006-01-01 00:00:00
Example 6.
Select DateTime (' Now ', ' start of Month ');
Result: 2006-10-01 00:00:00
Example 7.
Select DateTime (' Now ', ' start of Day ');
Result: 2006-10-17 00:00:00
Example 8.
Select DateTime (' Now ', ' +10 hour ', ' start of day ', ' +10 Hour ');
Result: 2006-10-17 10:00:00
Example 9.
Select DateTime (' Now ', ' localtime ');
Result: 2006-10-17 21:21:47
Example 10.
Select DateTime (' Now ', ' +8 Hour ');
Results: 2006-10-17 21:24:45 the +1 hour and -12 minute in example 3 indicate that a certain amount of time can be increased or decreased at the base time (the first parameter of the DateTime function).
The start of year in Example 5 represents the time of the beginning of the first day.
As can be seen from Example 8, although the 2nd parameter added 10 hours, but was the 3rd parameter "start of day" to zero the time to 00:00:00, followed by the 4th parameter at 00:00:00
On the basis of the time increased by 10 hours into the 10:00:00.

Example 9 converts the Greenwich time zone to the cost of the time zone.

Example 10 converts the Greenwich time zone to the East eight zone.

The strftime () function converts date strings in YYYY-MM-DD HH:MM:SS format to other forms of strings.
The syntax of Strftime () is strftime (format, date/time, modifier, modifier, ...)

It can be formatted with the following symbols for dates and times:
%d month, 01-31
%f decimal form of the second, SS. Sss
%H hours, 00-23
%j figure out the day of the year, 001-366
%m month, 00-12
%M min, 00-59
%s number of seconds from January 1, 1970 to present
%s seconds, 00-59
%w Week, 0-6 (0 is Sunday)
%W calculates the day of the week that falls within that year, 01-53
%Y years, YYYY
Percent hundred percent semicolon
Examples of the use of strftime () are as follows: Example 11.
Select Strftime ('%y.%m.%d%h:%m:%s ', ' Now ', ' localtime ');
Result: 2006.10.17 21:41:09
Example 11 uses a dot as the date separator, and converts the time to the local time zone.
For more information on SQLite date-time functions, refer to "Working with Dates and" in "SQLite" (Isbn:0-672-32685-x) written by Chris Newman.
Times "article

Transferred from: http://www.cnblogs.com/xmphoenix/archive/2011/05/23/2054022.html

SQLite date Type "Go"