Sqlite 简明教程

SQLite - Date & Time

SQLite 支持以下五个日期和时间函数：

SQLite supports five date and time functions as follows −

Sr.No.

Function

Example

1

date(timestring, modifiers…​)

This returns the date in this format: YYYY-MM-DD

2

time(timestring, modifiers…​)

This returns the time as HH:MM:SS

3

datetime(timestring, modifiers…​)

This returns YYYY-MM-DD HH:MM:SS

4

julianday(timestring, modifiers…​)

This returns the number of days since noon in Greenwich on November 24, 4714 B.C.

5

strftime(timestring, modifiers…​)

This returns the date formatted according to the format string specified as the first argument formatted as per formatters explained below.

上面提到的所有五个日期和时间函数都采用时间字符串作为参数。时间字符串后跟零个或多个修饰符。strftime() 函数还将格式字符串作为其第一个参数。以下部分将详细介绍不同类型时间字符串和修饰符。

All the above five date and time functions take a time string as an argument. The time string is followed by zero or more modifiers. The strftime() function also takes a format string as its first argument. Following section will give you detail on different types of time strings and modifiers.

Time Strings

时间字符串可以采用以下任何格式：

A time string can be in any of the following formats −

Sr.No.

Time String

Example

1

YYYY-MM-DD

2010-12-30

2

YYYY-MM-DD HH:MM

2010-12-30 12:10

3

YYYY-MM-DD HH:MM:SS.SSS

2010-12-30 12:10:04.100

4

MM-DD-YYYY HH:MM

30-12-2010 12:10

5

HH:MM

12:10

6

YYYY-MM-DD*T*HH:MM

2010-12-30 12:10

7

HH:MM:SS

12:10:01

8

YYYYMMDD HHMMSS

20101230 121001

9

now

2013-05-07

您可以使用“T”作为分隔日期和时间的文字字符。

You can use the "T" as a literal character separating the date and the time.

Modifiers

时间字符串后可以跟零个或多个修饰符，这些修饰符将改变上述五个函数中任何一个返回的日期和/或时间。修改器从左向右应用。

The time string can be followed by zero or more modifiers that will alter date and/or time returned by any of the above five functions. Modifiers are applied from the left to right.

SQLite 中提供了以下修改器：

Following modifers are available in SQLite −

  1. NNN days

  2. NNN hours

  3. NNN minutes

  4. NNN.NNNN seconds

  5. NNN months

  6. NNN years

  7. start of month

  8. start of year

  9. start of day

  10. weekday N

  11. unixepoch

  12. localtime

  13. utc

Formatters

SQLite 提供了一个非常方便的功能 strftime() 来格式化任何日期和时间。您可以使用以下替换来格式化您的日期和时间。

SQLite provides a very handy function strftime() to format any date and time. You can use the following substitutions to format your date and time.

Substitution

Description

%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

%S

Seconds, 00-59

%w

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

%W

Week of year, 01-53

%Y

Year, YYYY

%%

% symbol

Examples

现在让我们使用 SQLite 提示尝试各种示例。以下命令计算当前日期。

Let’s try various examples now using SQLite prompt. Following command computes the current date.

sqlite> SELECT date('now');
2013-05-07

以下命令计算当前月份的最后一天。

Following command computes the last day of the current month.

sqlite> SELECT date('now','start of month','+1 month','-1 day');
2013-05-31

以下命令计算给定 UNIX 时间戳 1092941466 的日期和时间。

Following command computes the date and time for a given UNIX timestamp 1092941466.

sqlite> SELECT datetime(1092941466, 'unixepoch');
2004-08-19 18:51:06

以下命令计算给定 UNIX 时间戳 1092941466 的日期和时间，并补偿您当地的时区。

Following command computes the date and time for a given UNIX timestamp 1092941466 and compensate for your local timezone.

sqlite> SELECT datetime(1092941466, 'unixepoch', 'localtime');
2004-08-19 13:51:06

以下命令计算当前 UNIX 时间戳。

Following command computes the current UNIX timestamp.

sqlite> SELECT strftime('%s','now');
1393348134

以下命令计算自美国独立宣言签署以来的天数。

Following command computes the number of days since the signing of the US Declaration of Independence.

sqlite> SELECT julianday('now') - julianday('1776-07-04');
86798.7094695023

以下命令计算自 2004 年某个时刻以来的秒数。

Following command computes the number of seconds since a particular moment in 2004.

sqlite> SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56');
295001572

以下命令计算本年度十月第一个星期二的日期。

Following command computes the date of the first Tuesday in October for the current year.

sqlite> SELECT date('now','start of year','+9 months','weekday 2');
2013-10-01

以下命令计算自 UNIX 纪元以来的时间，单位为秒（类似于 strftime('%s', 'now')，但包括小数部分）。

Following command computes the time since the UNIX epoch in seconds (like strftime('%s','now') except includes fractional part).

sqlite> SELECT (julianday('now') - 2440587.5)*86400.0;
1367926077.12598

在对日期进行格式化时，若要在 UTC 和本地时间值之间进行转换，请按如下方式使用 utc 或 localtime 修饰符 -

To convert between UTC and local time values when formatting a date, use the utc or localtime modifiers as follows −

sqlite> SELECT time('12:00', 'localtime');
05:00:00
sqlite> SELECT time('12:00', 'utc');
19:00:00