php的sqlite表格如何获取时间,SQLite 日期时间函数

SQLite并没有datatime字段类型，但是可以在字符串类型字段中存储时间，

并提供了一些比较实用的日期时间操作函数

strftime(日期时间格式, 日期时间字符串, 修正符, 修正符, ……)

strftime( 日期时间格式, 日期时间字符串 ) 也就等价于aardio中的:

time( 日期时间字符串,日期时间格式 ) ,sqlite与aardio 使用的格式化语法也一样。

参考：http://bbs.aardio.com/doc/reference/libraries/kernel/time/time.html

strftime() 函数返回一个经过格式化的日期时间，

它可以用下面的符号对日期和时间进行格式化：

%d  一月中的第几天 01-31

%f  小数形式的秒，SS.SSSS

%H  小时 00-24

%j  一年中的第几天 01-366

%J  Julian Day Numbers

%m  月份 01-12

%M  分钟 00-59

%s  从 1970-01-01日开始计算的秒数

%S  秒 00-59

%w  星期，0-6，0是星期天

%W  一年中的第几周 00-53

%Y  年份 0000-9999

%%  % 百分号

date,time,datetime,julianday 函数

date(日期时间字符串, 修正符, 修正符, ……)  等价于  strftime(“%Y-%m-%d”,…) 返回一个以 “YYYY-MM-DD” 为格式的日期

time(日期时间字符串, 修正符, 修正符, ……)  等价于 strftime(“%H:%M:%S”,…)  返回一个以 “HH:MM:SS” 为格式的日期时间；

datetime(日期时间字符串, 修正符, 修正符, ……) 等价于  strftime(“%Y-%m-%d %H:%M:%S”,…)  返回一个以 “YYYY-MM-DD HH:MM:SS” 为格式的日期时间；

julianday(日期时间字符串, 修正符, 修正符, ……) 等价于 strftime(“%J”,…) 返回儒略日(Julian day，JD)，儒略日指由公元前4713年1月1日，协调世界时中午12时开始所经过的天数，多为天文学家采用，用以作为天文学的单一历法，把不同历法的年表统一起来。如果计算相隔若干年两个日期之间的天数，利用儒略日就比较方便。

日期时间字符串

可以用以下几种格式：

格式有严格的要求 2008-06-15 03:35:28 日期只能用'-'分隔，时间只能用':' 分隔，不足二位数的必须补零

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

在第五种到第七种格式(ISO8601)中的“T”是一个分割日期和时间的字符；

第八种到第十种格式只代表2000-01-01日的时间，

第十一种格式的’now’表示返回一个当前的日期和时间，使用格林威治时间(UTC)；

第十二种格式表示一个 Julian Day Numbers。

修正符

日期和时间可以使用下面的修正符来更改日期或时间：

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前六个修正符就是简单的增加指定数值的时间和日期；第七到第十个修正符表示返回当前日期的开始；第十一个修正符表示返回下一个星期是N的日期和时间；第十二个修正符表示返回从1970-01-01开始算起的秒数；第十三个修正符表示返回本地时间。

下面举一些例子：

计算机当前时间

SELECT date(‘now’)

计算机当前月份的最后一天

SELECT date(‘now’,’start of month’,’+1 month’,’-1 day’)

计算UNIX 时间戳1092941466表示的日期和时间

SELECT datetime(‘1092941466’,’unixepoch’)

计算 UNIX 时间戳1092941466 表示的本地日期和时间

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

计算机当前UNIX 时间戳

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

两个日期之间相差多少天

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

两个日期时间之间相差多少秒

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

计算今年十月份第一个星期二的日期

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

取大于现在时间的数据

select * from 表 where 日期字段>datetime('now','localtime')

比较日期指定部分，举一反三，同样使用strftime格式式日期来对日、周、年比较

select * from 表 where strftime('%m',日期字段)=strftime('%m','now')

大于指定时间的第一条

select title,pubtime from article where pubtime>'2008-06-15 03:35:28' order by pubtime asc Limit 1 Offset 0

小于指定时间的第一条

select title,pubtime from article where pubtime

简单示例:

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

Sqlite Working with Dates and Times

In our sample database we have chosen to use integers for columns that store a date value, represented by the format YYYYMMDD. This format is fairly readable and, because the most significant part (the year) comes first, allows arithmetic comparisons to be performed. For instance just as February 29th 2004 is earlier than March 1st, 20040229 is a smaller number than 20040301.

This technique is not without its limitations. First, there is no validation on the values stored. Although February 29th is a valid date in the leap year 2004, it does not exist three years out of four and the value 20050229 is not a real date, yet could still be stored in the integer column or compared to a real date.

In fact even if you used a trigger to make the number eight digits long and also fall within a sensible year range, there are many values that could still be stored that do not represent dates on the calendar. Very strict checking would be required in your application program to ensure such date information was valid.

Similarly, you cannot perform date arithmetic using integer dates. Although 20040101 + 7 gives a date seven days later, 20040330 + 7 would give a number that looks like March 37th.

We have not even looked at a data type to store a time value yet, but the same limitations apply if a numeric field is used. SQLite contains a number of functions that allow you to work with both dates and times stored as character strings, allowing you to manipulate the values in useful ways.Valid Timestring Formats

SQLite is fairly flexible about the format in which you can specify a date and/or time. The valid time string formats are shown in the following list: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

DDDD.DDDD

For the format strings that only specify a time, the date is assumed to be 2000-01-01. Where no time is specified, midday is used. Simply using the string now tells SQLite to use the current date and time.

The format string DDDD.DDDD represents a Julian day numberthe number of days since noon on November 24, 4714 BC, Greenwich Mean Time. SQLite uses Julian date format internally to manipulate date and time values.Displaying a Formatted Date and Time

The core date and time function in SQLite is strftime(), which has the following prototype:strftime(format, timestring, modifier, modifier, ...)

This function is based upon the C function strftime() and the format parameter will accept most, although not all, of the same conversion specifiers. The following example shows how a date can be reformatted to MM/DD/YY format using strftime().sqlite> SELECT strftime('%m/%d/%Y', '2004-10-31');10/31/2004

Table 3.3 lists the conversions that can be performed by SQLite on a timestring.Table 3.3. Date and Time Conversion Specifiers

String

Meaning

%d

Day of month, 01-31

%f

Fractional seconds, SS.SSS

%H

Hour, 00-23

%j

Day of year, 001-366

%J

Julian day number, DDDD.DDDD

%m

Month, 00-12

%M

Minute, 00-59

%s

Seconds since 1970-01-01 (unix epoch)

%S

Seconds, 00-59

%w

Day of week, 0-6 (0 is Sunday)

%W

Week of year, 01-53

%Y

Year, YYYY

%%

% symbol

Date and Time Modifiers

Given one or more optional modifier arguments, strftime() can perform a calculation on the date given in timestring.

To add or subtract a period of time, the days, hours, minutes, seconds, months and years modifiers can be used, as shown in these examples:sqlite> SELECT strftime('%Y-%m-%d', '2004-10-31', '+7 days');2004-11-07sqlite> SELECT strftime('%H:%M', '22:00', '+12 hours');10:00sqlite> SELECT strftime('%Y-%m-%d %H:%M:%S','2004-01-01 00:00:00', '-1 second', '+1 year');2004-12-31 23:59:59

Note

The modifier keywords can be written as either singular or plural. In the last of the preceding examples, we used 1 second and 1 year rather than 1 seconds and 1 years for readability. SQLite does not understand English grammar, so either is always acceptable.

In these examples we have used the same output format as the original timestring to return the date information in a format that can be recognized by SQLite. You should only format the date differently when you want to display it in your application in a particular way.

To save having to enter the same format strings repeatedly when working with dates, SQLite provides four convenience functions that call strftime() with predefined formats.

Use date() to return a date with the format string %Y-%m-%d and time() to return a time as %H:%S. The function datetime() returns the date and time using these two formats combined. Finally julianday() uses the %J format specifier to return the Julian day number.

The arguments to all four functions are the same as strftime() except that the format argument is omitted. The following example uses datetime() to produce a more concise SQL statement:sqlite> SELECT datetime('2004-01-01 00:00:00', '-1 second', '+1 year');2004-12-31 23:59:59

Other modifiers allow you to adjust a date or time to the nearest significant value. Specifying start of month, start of year, or start of day will decrease the value given in timestring to midnight on the first of the month or year, or on that day respectively.

When executed on any day during 2004, the start of year modifier returns 2004-01-01, as shown in the following example:sqlite> SELECT datetime('now', 'start of year');2004-01-01 00:00:00

Modifiers are applied to timestring in the order they appear in the statement, as shown in the following example. Note that had the second statement been executed on the last day of the month, the result would have been differentthe start of the following month would have been returned.sqlite> SELECT datetime('now', 'start of month', '+1 day');2004-07-02 00:00:00sqlite> SELECT datetime('now', '+1 day', 'start of month');2004-07-01 00:00:00

Any number of modifiers can be combined, giving you considerable power when working with dates and times. For instance, the last day of the current month can be found using three modifiers in succession.sqlite> SELECT date('now', '+1 month', 'start of month', '-1 day');2004-07-31

Handling Different Time Zones

The locale settings of your system will determine which time zone is used when displaying dates and times; however, the underlying system clock will use Coordinated Universal Time (UTC), also known as Greenwich Mean Time (GMT)Greenwich Mean Time (GMT). Your time zone setting will specify a number of hours to be added to or subtracted from the UTC value to arrive at the correct local time.

For instance, to find the local time in New York you have to subtract five hours from UTC, or four hours during daylight savings time. Even in Greenwich, the local time is UTC + 1 hour during the summer months.

To convert between UTC and local time values when formatting a date, use the utc or localtime modifiers. The following examples were run on a system with the timezone set to Eastern Standard Time (UTC 5 hours).sqlite> SELECT time('12:00', 'localtime');2000-01-01 07:00:00sqlite> SELECT time('12:00', 'utc');2000-01-01 17:00:00

SQLite Date And Time Functions http://www.sqlite.org/lang_datefunc.html