sqlserver日期减一天_Chapter 02. 使用日期/时间函数和操作符

最新推荐文章于 2023-04-04 15:30:53 发布

eternal?

最新推荐文章于 2023-04-04 15:30:53 发布

阅读量2.4k

点赞数

文章标签： sqlserver日期减一天 trunc怎么进行日期相减 xsl日期减一天参数时间相减

课程：DataCamp_Skill Track_SQL fundamentals【笔记】

Chapter 02. 使用日期/时间函数和操作符 Working with DATE/TIME Functions and Operators

探索如何操作和查询日期和时间对象，包括如何在查询中使用当前时间戳，如何从现有日期和时间字段中提取子字段以及执行日期和时间算术运算。

2.1 基本算术运算符概述 Overview of basic arithmetic operators

已经了解了基本的数据类型，现在就来学习相关函数和操作以清洗和处理各种类型的数据，这一章先从时间和日期数据开始

具体而言，这一章学习的内容包括：

针对于时间日期数据的基本加减乘除算术运算
获取当前时间的函数：CURRENT_DATE``CURRENT_TIMESTAMP``NOW()
获取时间差、时间间隔的函数 AGE()
提取时间子域的函数：EXTRACT()``DATE_PART()``DATE_TRUNC()

在此之前，首先要注意不同的时间日期类型，运算的返回值时不一样的，以 DATE 和 TIMESTAMP 这两个最常用的来对比：DATE 值之差是整数 INTEGER，DATE 也可以直接加减整数进行相对运算（relative calculations）来得到前后某日期：（可看作这里的整数默认单位为一天）

SELECT date '2005-09-11' - date '2005-09-10';

+---------+
| integer |
|---------|
| 1       |
+---------+

SELECT date '2005-09-11' + integer '3';

+------------+
| date       |
|------------|
| 2005-09-14 |
+------------+

注意 PostgreSQL 里这种表示某数据类型的特定值的方式date '2005-09-11'/ integer '3'

相反，TIMESTAMP 值之差则为时间间隔 INTERVAL，TIMESTAMP 也可以直接加减 INTERVAL 进行相对运算（relative calculations）来得到前后某个时间点，另外注意时间间隔 INTERVAL 和整数 INTERGER 一样也是可以乘除一定倍数的：

SELECT date '2005-09-11 00:00:00' - date '2005-09-09 12:00:00';

+----------------+
| interval       |
|----------------|
| 1 day 12:00:00 |
+----------------+

SELECT rental_date + INTERVAL '3 days' as expected_return
FROM rental;

+---------------------+
| expected_return     |
|---------------------|
| 2005-05-27 22:53:30 |
+---------------------+

SELECT timestamp '2019-05-01' + 21 * INTERVAL '1 day';
-- 注意 '3 days' 和 '1 day' 单复数是有加不加s的区别的

+----------------------------+
| timestamp without timezone |
|----------------------------|
| 2019-05-22 00:00:00        |
+----------------------------+

另外，计算时间间隔除了直接相减，也可以用 AGE 函数

SELECT AGE(timestamp '2005-09-11 00:00:00', timestamp '2005-09-09 12:00:00');

+----------------+
| interval       |
|----------------|
| 1 day 12:00:00 |
+----------------+

注意 AGE 函数是第一参数减第二参数，若省略第二参数则默认减去目前时间，等于是计算“年龄”

如，我们会发现本科所用的数据库非常老，里面的租赁时间距今已13年了：

SELECT
    AGE(rental_date)
FROM rental;

+-----------------------------------+
| age                               |
|-----------------------------------|
| 13 years 11 mons 12 days 01:06:30 |
| 13 years 11 mons 12 days 01:05:27 |
| 13 years 11 mons 12 days 00:56:21 |
+-----------------------------------+

2.2 加上和减去日期和时间值 Adding and subtracting date and time values

获得各次租借的电影名、规定租借时间和实际租借时间

注意前两个来源于电影表films而最后一个来源于租借表rental，两者没有直接联系，需要通过存货表inventory间接关联

另外实际租借时间可以直接相减也可以用 AGE 函数

SELECT f.title, f.rental_duration,
    -- Calculate the number of days rented
    r.return_date - r.rental_date AS days_rented
    /AGE(r.return_date, r.rental_date) AS days_rented
FROM film AS f
    INNER JOIN inventory AS i ON f.film_id = i.film_id
    INNER JOIN rental AS r ON i.inventory_id = r.inventory_id
ORDER BY f.title;

注意，因为有些租借尚未归还没有归还日期所以算出来的实际租借时间也是null，之后会探讨为什么是这样

通过以下语句，我们可以查到 rental_date 和 return_date 的数据类型均为 timestamp

SELECT
    column_name,
    data_type
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_name = 'rental'
    AND column_name LIKE '%_date'
    -- 注意反义操作符的使用

2.3 间隔运算 INTERVAL arithmetic

如果你真的经营一家DVD租赁店，有时你可能需要哪些电影目前有顾客正在租借中，这个数据集中，rental_date 为空的正是那些正在出租而尚未归还的记录

这里，我们用 WHERE 排除掉这些尚未归还的记录，对于剩下的记录，查询其电影名称、允许租借时间和实际租借时间

其中，要将允许租借时间 rental_duration 转换为 INTERVAL 类型（其原本为 SMALLINT 小整数类型），其中一个方法之前说过，就是乘以 INTERVAL ‘1’ DAY

SELECT
    f.title,
    -- Convert the rental_duration to an interval
    【INTERVAL '1' DAY * f.rental_duration,】
    -- Calculate the days rented as we did previously
    r.return_date - r.rental_date AS days_rented
FROM film AS f
    INNER JOIN inventory AS i ON f.film_id = i.film_id
    INNER JOIN rental AS r ON i.inventory_id = r.inventory_id
-- Filter the query to exclude outstanding rentals
【WHERE r.return_date IS NOT NULL】
ORDER BY f.title;

现在，我们在最后一个练习中，将它们放在一起以计算 expected_return_date

2.4 计算预计归还日期 Calculating the expected return date

将转换为 INTERVAL 后的 rental_duration 加在 rental_date 上得到预期归还日 expected_return_date

注意 rental_date 是 TIMESTAMP 类型，这也是为什么 rental_duration 要先转换成 INTERVAL 才能加上去

SELECT
    f.title,
    r.rental_date,
    f.rental_duration,
    -- Add the rental duration to the rental date
    INTERVAL '1' day * f.rental_duration + r.rental_date AS expected_return_date,
    r.return_date
FROM film AS f
    INNER JOIN inventory AS i ON f.film_id = i.film_id
    INNER JOIN rental AS r ON i.inventory_id = r.inventory_id
ORDER BY f.title;

现在，我们可以将 expected_return_date 与实际return_date进行比较，以确定某个租借记录是否是按时归还的。在下一个视频中，我们将学习如何在查询中使用当前日期和时间值。

2.5 检索当前日期/时间的函数 Functions for retrieving current date/time

实践中一个非常常用的功能是获取当前的时间，PostgreSQL 有里很多方法，这节课我们会介绍一些

对于获取当前的完整时间戳，可以用 NOW() 函数：

SELECT NOW();

+-------------------------------+
| now()                         |
|-------------------------------|
| 2019-04-19 02:51:18.448641+00 |
+-------------------------------+

但上面这个得到的时间戳是带时区的（后面的 +00 代表时区），我们一般要把它转换为不带时区的时间戳：

SELECT NOW()::timestamp;

+----------------------------+
| now()                      |
|----------------------------|
| 2019-04-19 02:51:18.448641 |
+----------------------------+

注意，投射/数据类型强制转换（CASTING）可以将一个数据类型转换为另一个数据类型，当我们查询某列数据（及其运算结果）并要把结果以某种特定数据类型输出时会很有用。

对于 PostgreSQL 来说，可以用上面这种加双冒号和数据类型名的方式来转换，之前也看过很多次了

对于标准 SQL 语言，要用 CAST 函数：

SELECT CAST(NOW() as timestamp);

和 NOW 函数类似，CURRENT_TIMESTAMP 也可以获取当前完整时间戳：

SELECT 
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP :: TIMESTAMP,
    CAST(CURRENT_TIMESTAMP AS TIMESTAMP);

/*
2020-11-15 09:29:41.791520+00:00    
2020-11-15 09:29:41.791520  
2020-11-15 09:29:41.791520
*/

但 CURRENT_TIMESTAMP 和比 NOW 多一点灵活性，它可以添加一个参数设置秒数的精度（四舍五入）：

SELECT CURRENT_TIMESTAMP(2);

+-------------------------------+
| current_timestamp             |
|-------------------------------|
| 2019-04-19 02:51:18.44+00     |
+-------------------------------+

如果不是要获得完整的时间戳，而只想得到当前的日期或一天内的时间，可以用 CURRENT_DATE 和 CURRENT_TIME：

SELECT CURRENT_DATE;

+--------------+
| current_date |
|--------------|
| 2019-04-19   |
+--------------+

SELECT CURRENT_TIME;

+------------------------+
| current_time           |
|------------------------|
| 04:06:30.929845+00:00  |
+------------------------+

2.6 当前时间戳函数 Current timestamp functions

测试这四种获取当前时间日期的函数并观察它们各自的特征

SELECT 
    NOW(),
    CURRENT_TIMESTAMP,
    CURRENT_DATE,
    CURRENT_TIME;

会发现，NOW 和 CURRENT_TIMESTAMP 都是默认附带时区的完整时间戳，最后两个一个只有日期没时间，一个只有时间没日期（另外 CURRENT_TIME 好像也默认附带时区）

2.7 使用当前日期和时间 Working with the current date and time

获取当前时间或日期并按需进行转换是实践中很常用的功能

-- Select the current timestamp
SELECT NOW();

-- Select the current date
SELECT CURRENT_DATE;

--Select the current timestamp without a timezone
SELECT CASt( NOW() AS timestamp )

SELECT 
    -- Select the current date
    CURRENT_DATE,
    -- CAST the result of the NOW() function to a date
    CAST( NOW() AS date )

注意 CAST 可以进行各种转换，最后一个语句可以看出 CURRENT_DATE 和 CAST( NOW() AS date ) 是等效的，结果均为 2020-11-16

2.8 操作当前日期和时间 Manipulating the current date and time

这一次不用 NOW 而用换用 CURRENT_TIME，精度取到秒的整数位，用 PostgreSQL 的简便方式（双冒号）强制转换为没有时区的timestamp，并计算五天以后的时间

SELECT
    CURRENT_TIMESTAMP(0)::timestamp AS right_now,
    -- 双冒号前后的空格不是必须的
    interval '5 days' + CURRENT_TIMESTAMP(0) AS five_days_from_now;
    -- 可以加负号，即：- interval '5 days'，则计算五天前的时间

另外，实验发现从高精度向低精度强制转换数据类型和格式时，多余的信息不是被隐藏而是直接被舍去了，从下面这个例子可以看出：

SELECT 
    CURRENT_TIMESTAMP :: timestamp,
    -- 2020-11-16 03:01:33.608806   
    CURRENT_TIMESTAMP :: date :: timestamp;
    -- 2020-11-16 00:00:00

再次提醒，获取和处理当前日期和时间是一个实践中会经常用到的功能

2.9 提取和转换日期/时间数据 Extracting and transforming date/ time data

默认的时间戳精度（微秒级）对于大部分分析来说都比较冗余

对于时间戳 TIMESTAMP、时间 TIME、间隔 INTERVAL 数据（好像日期 DATE 不行？），可以用 EXTRACT()、DATE_PART() 来提取其中的子字段（如年、月、日、星期几等，提取结果为数字或文本，供之后的分组分析等使用，比如：2005 or 5 or 2 or Friday ），也可以用 DATE_TRUNC() 来截取特定精度以标准化（结果仍为 TIMESTAMP 和 INTERVAL 等，只是精度变了，如，精确到月：2005-05-00 00:00:00 ）

EXTRACT() 和 DATE_PART() 功能和语法都很相似，有细微区别。两者都需要传入两个参数，一是域识别符（field identifier，指明要从源数据中提取的部分），二是源数据（source，必须是 TIME、TIMESTAMP、INTERVAL 类型）

/*
EXTRACT( field FROM source )
【注意这个函数两个参数是 FROM 相联而非逗号隔开的，很特别，要记住】
【而且域标识符没有引号】
*/
SELECT EXTRACT(quarter FROM timestamp '2005-01-24 05:12:00') AS quarter;

-- DATE_PART('field', source) 
-- 注意这一种则相反，没有 FROM 但要用单引号把域标识符括起来
SELECT DATE_PART('quarter', timestamp '2005-01-24 05:12:00') AS quarter;

两者结果都是：
+---------+
| quarter |
|---------|
| 1       |
+---------+

可以看到默认的付款记录表里 payment_date 是精确到秒的，假设我们要得到按年和季度的总金额，则要用到时间提取函数提取相应子段并作为分类依据

SELECT
    EXTRACT(quarter FROM payment_date) AS quarter,
    EXTRACT(year FROM payment_date) AS year,
    SUM(amount) AS total_payments
FROM payment
GROUP BY 1, 2;
/* 
当分类依据不是原始列而是 SELECT 里的“衍生列”时
用这种数字来指代相应“衍生列”是非常有用的简化方法
*/

再来两个用 DATE_TRUNC() 改变精度的例子：

-- Truncate timestamp '2005-05-21 15:30:30' by year  
SELECT DATE_TRUNC('year', TIMESTAMP '2005-05-21 15:30:30');
-- Result: 2005-01-01 00:00:00

-- Truncate timestamp '2005-05-21 15:30:30' by month
SELECT DATE_TRUNC('month', TIMESTAMP '2005-05-21 15:30:30');
-- Result: 2005-05-01 00:00:00

注意日期截取函数 DATE_TRUNC 的第一个参数指明了截取的时间日期的精度，这个截取函数的精度标识符参数（‘year’、‘month’、‘day’ 等）其实是之前两个提取函数里域标识符的一个子集

2.10 使用EXTRACT Using EXTRACT

EXTRACT 和 DATE_PART 可以从 timestamp 类型的来源域中提取子域

假设你想要建立一个根据星期几来预测DVD租借活动的模型，你可以使用 EXTRACT 函数和 dow（代表 ‘day of week’）域标识符来提取星期几并根据星期几来分组和统计

-- Extract day of week from rental_date
SELECT 
  EXTRACT(dow FROM rental_date) AS dayofweek, 
  -- Count the number of rentals
  COUNT(*) as rentals 
FROM rental 
GROUP BY 1;
/*
发现直接用 SELECT 中的衍生列别名来分组也是可以的，即：
GROUP BY dayofweek; 
那最好还是别用数字还是用列名，更加明确也不会因为SELECT的修改而出错
*/

EXTRACT 可以帮助您找出数据中的隐藏规律，例如一周中的哪几天是DVD租赁最繁忙的时间。

2.11 使用DATE_TRUNC Using DATE_TRUNC

按日、月或年来统计租借数量，首先要用时间截取函数来截取对应的时间精度

SELECT 
  DATE_TRUNC('day'/'month'/'year', rental_date) AS rental_day/month/year,
  -- Count total number of rentals 
  COUNT(*) AS rentals 
FROM rental
GROUP BY rental_day;
-- 最好用 GROUP BY rental_day/month/year

现在，您可以 DATE_TRUNC 用来操纵时间戳类型数据以创建特定精度的新字段

注意时间截取函数的结果只是精度变了，但仍然是时间戳 timestamp 或时间间隔 interval 类型数据而非像之前时间提取函数那样结果为数字或文本

2.12 全部放在一起 Putting it all together

获得特定时间段（从 2005-05-01 之后的 90 天）的顾客租借记录，要求获得顾客全名、电影名、租借日期、星期几、实际租借天数、是否逾期

这样一个结果集可以用来查看星期几租借最多以及分析星期几租借的DVD逾期归还的可能性最大

SELECT 
  c.first_name || ' ' || c.last_name AS customer_name,
  -- 字符串连接操作，下一章会学
  f.title,
  r.rental_date,
  -- Extract the day of week date part from the rental_date
  EXTRACT(dow FROM r.rental_date) AS dayofweek,
  AGE(r.return_date, r.rental_date) AS rental_days,
  -- Use DATE_TRUNC to get days from the AGE function
  CASE WHEN DATE_PART('day', AGE(r.return_date, r.rental_date)) > 
  -- Calculate number of d
    f.rental_duration
    /*
    注意这里不能 f.rental_duration * INTERVAL '1 day'
    因为大于号前面是数字，类型一样才能比较
    这个不等式还可以是：
    CASE WHEN DATE_TRUNC('day', AGE(r.return_date, r.rental_date)) > 
    f.rental_duration * INTERVAL '1 day'
    总之，要数字与数字比较，INTERVAL 与 INTERVAL 比较，类型一样才能比较
    */
  THEN TRUE 
  ELSE FALSE END AS past_due 
FROM 
  film AS f 
  INNER JOIN inventory AS i 
    ON f.film_id = i.film_id 
  INNER JOIN rental AS r 
    ON i.inventory_id = r.inventory_id 
  INNER JOIN customer AS c 
    ON c.customer_id = r.customer_id 
  /*
  其实整个数据库是以 rental 租借记录表为核心的，
  向左连接一对多连接库存表，进而连接电影表
  向右连接顾客表
  这些表可以都看作是租借表的查询表，为租借记录提供重复的详细信息
  可根据需要连接成租借详情表
  */
WHERE 
  -- Use an INTERVAL for the upper bound of the rental_date 
  r.rental_date BETWEEN CAST('2005-05-01' AS DATE) 
  AND CAST('2005-05-01' AS DATE) + INTERVAL '90 day';
  /*
  用CAST麻烦了（但可能更通用），从之前的一些例子知道，
  要表示 2005-05-01 这个日期，直接写成 date '2005-05-01' 就可以了
  最后的WHERE语句因此可以简化为：
  r.rental_date BETWEEN date '2005-05-01' 
  AND date '2005-05-01' + INTERVAL '90 day';
  经实验发现确实没问题
  但也许这种简化表示法是PostgreSQL特有的而非SQL通用的标准方法
  所以更麻烦的CAST法也要记住
  */

很棒！PostgreSQL日期/时间函数提供了用于处理，清理和转换事务数据（ transactional data）的强大工具。