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
1	Geitost	2008/11/11
2	Camembert Pierrot	2008/11/9
3	Mozzarella di Giovanni	2008/11/11
4	Mascarpone Fabioli	2008/10/29

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

我们使用下面的 SELECT 语句：

OrderId	ProductName	OrderDate
1	Geitost	2008/11/11
3	Mozzarella di Giovanni	2008/11/11

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

OrderId	ProductName	OrderDate
1	Geitost	2008/11/11 13:23
2	Camembert Pierrot	2008/11/9 15:45
3	Mozzarella di Giovanni	2008/11/11 11:12
4	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

Id	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数据库

Id	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

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

执行输出结果

10

练习题: 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