Level 4 时间

1. 日期 Date

当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，与数据库中日期列的格式相匹配。

只要您的数据包含的只是日期部分，运行查询就不会出问题。但是，如果涉及时间部分，情况就有点复杂了。

在讨论日期查询的复杂性之前，我们先来看看最重要的内建日期处理函数。

1.1 SQL DATE 函数

1.1.1 MySQL Date 函数

下面的表格列出了 MySQL 中最重要的内建日期函数：

函数

描述

NOW()

返回当前的日期和时间

CURDATE()

返回当前的日期

CURTIME()

返回当前的时间

DATE()

提取日期或日期/时间表达式的日期部分

EXTRACT()

返回日期/时间的单独部分

DATE_ADD()

向日期添加指定的时间间隔

DATE_SUB()

从日期减去指定的时间间隔

DATEDIFF()

返回两个日期之间的天数

DATE_FORMAT()

用不同的格式显示日期/时间

1.1.2 SQL Server Date 函数

下面的表格列出了 SQL Server 中最重要的内建日期函数：

函数

描述

GETDATE()

返回当前的日期和时间

DATEPART()

返回日期/时间的单独部分

DATEADD()

在日期中添加或减去指定的时间间隔

DATEDIFF()

返回两个日期之间的时间

CONVERT()

用不同的格式显示日期/时间

1.2 SQL Date 数据类型

MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：

DATE - 格式：YYYY-MM-DD
DATETIME - 格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS
YEAR - 格式：YYYY 或 YY

SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：

DATE - 格式：YYYY-MM-DD
DATETIME - 格式：YYYY-MM-DD HH:MM:SS
SMALLDATETIME - 格式：YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式：唯一的数字

注释：当您在数据库中创建一个新表时，需要为列选择数据类型！

1.3 SQL 日期处理

如果不涉及时间部分，那么我们可以轻松地比较两个日期！

假设我们有如下的 "Orders" 表：

OrderId

ProductName

OrderDate

Geitost

2008/11/11

Camembert Pierrot

2008/11/9

Mozzarella di Giovanni

2008/11/11

Mascarpone Fabioli

2008/10/29

现在，我们希望从上表中选取 OrderDate 为 "2008-11-11" 的记录。

我们使用下面的 SELECT 语句：

OrderId

ProductName

OrderDate

Geitost

2008/11/11

Mozzarella di Giovanni

2008/11/11

现在，假设 "Orders" 表如下所示

OrderId

ProductName

OrderDate

Geitost

2008/11/11 13:23

Camembert Pierrot

2008/11/9 15:45

Mozzarella di Giovanni

2008/11/11 11:12

Mascarpone Fabioli

2008/10/29 14:56

如果我们使用和上面一样的 SELECT 语句：

SELECT * FROM Orders WHERE OrderDate='2008-11-11'
-- 或
SELECT * FROM Orders WHERE OrderDate='2008-11-11 00：00：00'

那么我们将得不到结果！因为表中没有"2008-11-11 00:00:00"日期。如果没有时间部分，默认时间为 00:00:00。

提示：如果您希望使查询简单且更易维护，那么请不要在日期中使用时间部分！

2. 日期函数的实例

前面介绍了下日期以及日期有哪些函数，接下里我们学习下如何在SQL中如何使用这些函数。因为时间的概念不仅在实际中，在数据库中复杂，因此下面我们用实例的方式，带领大家学习具体的应用场景。

2.1 NOW()

NOW()可以用来返回当前日期和时间

NOW 实例

SELECT NOW();

执行输出结果

2017-01-13 08:03:52

练习题: NOW

题目描述：返回当前时间

-- WRITE YOUR SQL HERE

2.2 CURDATE()

CURDATE()可以用来返回当前日期

CURDATE 实例

SELECT CURDATE();

执行输出结果

2017-01-13

练习题：CURDATE

题目描述：返回当前日期

-- write your sql here

2.3 CURTIME()

CURTIME()可以用来返回当前时间

CURTIME 实例

SELECT CURTIME();

执行输出结果

08:05:15

2.4 DATE()

DATE()可以用来提取包含日期或日期/时间的语句中的日期部分

DATE 实例

我们有如下测试表Test

Name

BirthTime

4120

Pratik

1996-09-26 16:44:15.581

SELECT Name, DATE(BirthTime) AS BirthDate FROM Test;

执行输出结果

Name    BirthDate
Pratik    1996-09-26

DATE 练习题

题目描述：现在我们lintcode数据库有一个课程表courses2，课程表里面课程创建时间是date/time形式，现在希望你能查询得到所有课程的创建日期

-- write your sql here

2.5 EXTRACT()

EXTRACT()用于返回date/time的某一部分

语法

EXTRACT(unit FROM date)

其中

unit可以是MICROSECOND(微秒), SECOND(秒), MINUTE(分钟), HOUR(时), DAY(天), WEEK(星期), MONTH(月), QUARTER(季), YEAR(年)等

演示数据库

我们有如下Test数据库

Name

BirthTime

4120

Pratik

1996-09-26 16:44:15.581

EXTRACT 实例 Ⅰ DAY

SELECT Name, Extract(DAY FROM BirthTime) AS BirthDay FROM Test;

执行输出结果

Name    BirthDay
Pratik    26

EXTRACT 实例 Ⅱ YEAR

SELECT Name, Extract(YEAR FROM BirthTime) AS BirthYear FROM Test;

执行输出结果

Name    BirthYear
Pratik    1996

EXTRACT 实例 Ⅲ SECOND

SELECT Name, Extract(SECOND FROM BirthTime) AS BirthSecond FROM Test;

执行输出结果

Name    BirthSecond
Pratik    581

3. 日期的运算

3.1 DATE_ADD

加入一个指定的时间间隔到date

语法

DATE_ADD(date, INTERVAL expr type);

其中

date 指代我们希望被操作的有效日期

INTERVAL 我们想加入到date上的间隔个数

type具体的数据类型，可以是 MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR 等

DATE_ADD 实例

测试数据表Test

Name

BirthTime

4120

Pratik

1996-09-26 16:44:15.581

SELECT Name, DATE_ADD(BirthTime, INTERVAL 1 YEAR) AS BirthTimeModified FROM Test;

执行输出结果

Name    BirthTimeModified
Pratik    1997-09-26 16:44:15.581

练习题：DATE_ADD

题目描述：给数据库lintcode课程表courses的所有课程创建日期加上1天

-- write your sql here

3.2 DATE_SUB()

DATE_SUB和DATE_ADD类似，只不过一个是加一个是减。

3.3 DATEDIFF()

返回两个日期的天数差

语法

DATEDIFF(date1, date2);
date1 & date2- date/time expression

DATEDIFF 实例

SELECT DATEDIFF('2017-01-13','2017-01-03') AS DateDiff;

执行输出结果

练习题: DATEDIFF

题目描述：lintcode数据库有课程表courses，请计算得出所有课程开课到现在的天数

-- write your sql here

3. 格式化输出日期

我们在SQL中使用DATE_FORMAT()方法来格式化输出date/time

语法

DATE_FORMAT(date,format);

其中

date一个有效日期

format是date/time的输出格式，可以使用以下方式

%a-Abbreviated weekday name (Sun-Sat)
%b-Abbreviated month name (Jan-Dec)
%c-Month, numeric (0-12)
%D-Day of month with English suffix (0th, 1st, 2nd, 3rd)
%d-Day of month, numeric (00-31)
%e-Day of month, numeric (0-31)
%f-Microseconds (000000-999999)
%H-Hour (00-23)
%h-Hour (01-12)
%I-Hour (01-12)
%i-Minutes, numeric (00-59)
%j-Day of year (001-366)
%k-Hour (0-23)
%l-Hour (1-12)
%M-Month name (January-December)
%m-Month, numeric (00-12)
%p-AM or PM
%r-Time, 12-hour (hh:mm:ss followed by AM or PM)
%S-Seconds (00-59)
%s-Seconds (00-59)
%T-Time, 24-hour (hh:mm:ss)
%U-Week (00-53) where Sunday is the first day of week
%u-Week (00-53) where Monday is the first day of week
%V-Week (01-53) where Sunday is the first day of week, used with %X
%v-Week (01-53) where Monday is the first day of week, used with %x
%W-Weekday name (Sunday-Saturday)
%w-Day of the week (0=Sunday, 6=Saturday)
%X-Year for the week where Sunday is the first day of week, four digits, used with %V
%x-Year for the week where Monday is the first day of week, four digits, used with %v
%Y-Year, numeric, four digits
%y-Year, numeric, two digits

DATE_FORMAT 实例

DATE_FORMAT(NOW(),'%d %b %y')

执行输出结果

13 Jan 17

练习题: DATE_FORMAT

题目描述: 使用格式化输出的方式返回lintcode课程表courses所有课程的创建日期，按照'年月'的形式显示

-- write your sql here

PreviousNULL()NextLevel 5 多表单联合

Last updated 4 years ago

Was this helpful?