时间日期型数据

小辉数据分析 2021-05-07

543

2.1获取当前日期
2.2获取当前的系统时间
2.3获取当前系统日期与时间
2.4从指定的日期时间中提取信息

2.4.1提取年、月份、月份中的第几天等信息
2.4.2提取季度、星期几、一年中的第几天等信息
2.4.3获取本周是一年中的第几周

2.5时间日期的相关计算

2.5.1加法运算
2.5.2减法运算
2.5.3两个日期之间做差

3.1PostgreSQL中日期与时间型数据
3.2三种工具中的日期时间型数据的表示
3.3将日期时间转换为文本

一.引言

时间日期型数据是编程语言中一种重要的数据类型，在我们的日常生活中拥有众多的应用。这一次，我们就把这种类型的数据大体讲解一下。这一次讲解，我们涉及到了三大工具：PostgreSQL、MySQL以及Excel。

二.三大工具中的时间日期型数据

2.1获取当前日期

在PostgreSQL以及MySQ中，其标准的语法结构都是：

select current_date;

在Excel中，获取当前日期，则要使用today()

在展示的结果上，两者也存在着一定的差别：

PostgreSQL与MySQL：

YYYY-MM-DD（四位数的年-月份-日期）

Excel:

YYYY/MM/DD（四位数的年/月份/日期）

2.2获取当前的系统时间

在PostgreSQL以及MySQL中使用的是：

select current_time;

在Excel中，我们则要使用函数now()

在PostgreSQL中，我们同样可以使用select localtime
来获取当前的系统时间，只不过，这一次的系统时间是不包含时区值的。

2.3获取当前系统日期与时间

-- PostgreSQL中的三种方法
select now();
select current_timestamp;
select localtimestamp;
-- MySQL中的两种方法
select now();
select current_timestamp;

Excel中则要使用NOW()函数来解决这一个问题了。

2.4从指定的日期时间中提取信息

有的时候，我们需要从一个日期或时间中提取一部分信息。比如说从日期中提取年份信息，月份信息，季度信息等，面对这些需求，我们该如何解决呢？

2.4.1提取年、月份、月份中的第几天等信息

在PostgreSQL中，提取这一部分信息比较简单，使用一个extract函数就可以解决了。

例如，面对如下的两个时间：

我们要提取如下的信息：

年度信息
月份信息
一月中的第几天
小时信息
分钟信息
秒信息

面对这样的需求，我们该怎么办呢？一个extract函数全部搞定。

select t,
extract(year from t) as years,
extract(month from t) as months,
extract(day from t) as days,
extract(hour from t) as hours,
extract(minute from t) as mins,
extract(second from t) as secs
from dt;

如果我们要使用MySQL来提取这些信息我们该怎么办呢？MySQL中其实是有extracr这一个万能函数的。不过，这一次，我们并不打算使用这一函数来解决问题。我们使用year()等专门的函数来解决这一问题。

select t,
year(t) as years,
month(t) as months,
day(t) as days,
hour(t) as hours,
minute(t) as mins,
second(t) as secs
from dt;

那么，我们如果使用Excel来获取这些信息，该怎么做呢？

方法其实和MySQL是一样的。同样是使用year()等函数来实现。

这里，我们要注意一下三种方法提取出来的信息都是数值型数据！

2.4.2提取季度、星期几、一年中的第几天等信息

如果我们使用的是PostgreSQL，我们仍然可以使用extract函数来解决这一个问题。

select t,
extract(doy from t) as dayofyear,# 返回一年中的第几天
extract(dow from t) as dayofweek,# 返回星期几
extract(quarter from t) as quarter # 返回季度信息
from dt;

如果我们使用的是MySQL，那么，我们可以使用如下的方法来解决：

select t,
dayofyear(t) as dayofyear,
dayofweek(t) as dayofweek,
quarter(t) as quarter
from dt;

如果我们要使用Excel实现这一功能，则要使用：

获取当前是星期几：使用weekday函数
获取当前的季度信息：使用IF+MONTH

其具体代码是=IF(MONTH(A2)<=3,1,IF(MONTH(A2)<=6,2,IF(MONTH(A2)<=9,3,4)))

注：Excel中暂时没有找到如何实现这是一年中的第几天的简易方法。

这里，细心的人可能会发现，三种工具得出的星期几数据是有点不同的，这是因为三种工具的星期起始判断是不同的：

PostgreSQL：星期日是第一天，但是从0开始计数。
MySQL：星期日是第一天，但是从1开始计数
Excel：总共有三种方式。

从星期日=1到星期六=7，用1.
从星期一=1到星期日=7，用2.
从星期一=0到星期日=6，用3.

2.4.3获取本周是一年中的第几周

在MySQL中，我们要使用weekofyear函数：

select weekofyear(t) from dt;

在Excel中，我们要使用weeknum函数。

“
不过需要注意的是，weeknum()函数默认星期日是一周中的第一天，比如2019-12-29是星期日，如果星期一是一周中的第一天，则这一天是第52周；如果星期日是一周中的第一天，则这一天是53周。
”

2.5时间日期的相关计算

2.5.1加法运算

如何实现如下需求：

指定日期和时间之后延后10天
指定日期和时间之后延后3小时
指定日期和时间之后延后15分钟

PostgreSQL实现：

select t,
t+interval '10 day' as after10days, # 10天后
t+interval '3 hour' as after3hours, # 3小时后
t+interval '15 minute' as after15minutes,# 15分钟后
t+interval '2 day 4 hour 12 minute 23 second' as newtimestamp
from dt;

在指定日期之后加入时间

insert into dt values('2014-03-15');
select t,
t+time'07:23:15' as newdatetime
from dt order by t desc limit 1;

MySQL实现：

select t,
date_add(t,interval 10 day)
from dt;

image-20210506113009542

Excel实现：

“
在Excel中，如果要对日期和时间进行向后偏移，直接在原日期和时间上加偏移量即可，默认的偏移单位是天，如果要偏移其他单位与天切换即可。比如，偏移一年，直接加365天即可；偏移一个月，直接加30天即可；偏移1小时，直接加1/24即可。
”

image-20210506114032774

2.5.2减法运算

PostgreSQL

select t,
t-interval '10 day' as before10days,
t-interval '3 hour' as before3hours,
t-interval '15 minute' as before15minutes,
t-interval '2 day 4 hour 12 minute 23 second' as newtimestamp
from dt;

MySQL

-- 第一种方法
select t,
date_sub(t,interval 10 day)
from dt;
-- 第二种方法
select t,
date_add(t,interval -10 day)
from dt;

2.5.3两个日期之间做差