击上方蓝字关注我们

今天是SQL知识大全的第五讲，主要内容是和时间函数相关，主要包括了常用的时间函数，时间提取函数，时间计算函数以及时间和时间戳之间的转换。

常用的时间函数

to_date()函数

to_date()函数将字符串的日期转换为时间格式的日期，转换后的时间格式可以是如下形式：

格式
意义
YEAR 年的拼写MM 数字月MONTH 月的全拼MON 月的缩写DD 数字日DAY 星期的全拼DY 星期的缩写AM 表示上午或者下午HH24、HH12 12小时制或24小时制‍MI 分钟‍‍SS 秒钟‍‍‍

具体示例如下：

SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL


SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL


SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL


SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL


SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL


SELECT TO_DATE('2006', 'YYYY') FROM DUAL

current_date()/current_time()

返回当前时间,某年某月某日，current_time()等同于current_date。

SELECT CURDATE()# 返回值：
# '2020-10-09'

current_timestamp()

current_timestamp()相当于now(),返回当前的时间戳。

时间提取

时间提取函数包括year(),month(),day(),hour(),minute(),second()等，其用法都是一直的，这些函数分别返回 date 的年份，月份，日期，时间等，范围为1000-9999。当 date 为0时，返回0。

具体示例如下：

SELECT YEAR('98-02-03')
# 返回值  1998
SELECT MONTH('98-02-03')
# 返回值  02
SELECT DAY('98-02-03')
# 返回值  3

时间计算函数

DATEDIFF()

语法：DATEDIFF(expr1,expr2)

DATEDIFF()将返回expr1 - expr2的值，用来表示两个日期相差的天数。expr1 和 expr2 都是日期或日期时间表达式。运算中只用到了这些值的日期部分。

SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30')


#返回结果
1

date_sub()/date_add()

语法格式：

DATE_ADD(date,INTERVAL expr unit)

DATE_SUB(date,INTERVAL expr unit)

执行日期计算的两种函数。date 是一个用来指定开始日期的 DATETIME 或 DATE 值。expr 是一种以字符串形式呈现的表达式，用来指定从开始日期增加或减少的间隔值。如果是负的间隔值，则 expr 值的第一个字符是-号。unit 是一个单位关键字，用来指定expr表达式应该采取的单位。

INTERVAL 关键字与单位说明符都不区分大小写。

下表列出了每个单位数值所对应的 expr 参数的期望格式。

单位所能取的值期望的expr格式MICROSECOND毫秒SECOND秒MINUTE分HOUR小时DAY日WEEK周MONTH月QUARTER季度YEAR年SECOND_MICROSECOND'秒.毫秒'MINUTE_MICROSECOND'分.毫秒'MINUTE_SECOND'分:秒'HOUR_MICROSECOND'小时.毫秒'HOUR_SECOND'小时:分:秒'HOUR_MINUTE'小时:分'DAY_MICROSECOND'日.毫秒'DAY_SECOND'日 小时:分:秒'DAY_MINUTE'日 小时:分'DAY_HOUR'日 小时'YEAR_MONTH'年-月'

时间戳

时间戳的定义

时间戳是从1970年1月1日（UTC/GMT的午夜）开始所经过的秒数，不考虑闰秒。

UNIX时间戳的0按照ISO 8601规范为 ：1970-01-01T00:00:00Z.

一个小时表示为UNIX时间戳格式为：3600秒；一天表示为UNIX时间戳为86400秒，闰秒不计算。

在大多数的UNIX系统中UNIX时间戳存储为32位，这样会引发2038年问题或Y2038。

UNIX_TIMESTAMP(date)

若无参数调用，则返回一个 Unix timestamp ('1970-01-01 00:00:00' GMT 之后的秒数) 作为无符号整数，得到当前时间戳 。

若用date 来调用 UNIX_TIMESTAMP()，它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回。date 可以是一个 DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD或YYYMMDD格式的数字。

例如:

SELECT UNIX_TIMESTAMP() ; （执行时的时间:2009-08-06 10:10:40）
# 1249524739


SELECT UNIX_TIMESTAMP('2009-08-06') ;
# 1249488000

from_unixtime(）

from_unixtime(t1,’yyyy-MM-dd HH:mm:ss’)

其中t1是10位的时间戳值，即1970-1-1至今的秒，而13位的所谓毫秒的是不可以的。

对于13位时间戳，需要截取，然后转换成bigint类型，因为from_unixtime类第一个参数只接受bigint类型。例如：

select from_unixtime(cast(substring(tistmp,1,10) as bigint),’yyyy-MM-dd HH’) tim ,count(*) cn from ttengine_hour_data 
where …


SELECT from_unixtime(cast(ts AS bigint))
FROM temptable

参考文章

https://wiki.jikexueyuan.com/project/mysql/useful-functions/time-functions.html

https://baike.baidu.com/item/unix%E6%97%B6%E9%97%B4%E6%88%B3

https://blog.csdn.net/qq_24309787/article/details/81095238

SQL系列文章持续更新中

往期推荐

史上最全的SQL知识点汇总，错过这次在等一年

SQL知识大全(一):数据库的语言分类你都知道吗？

SQL知识大全(二):SQL的基础知识你都掌握了吗？

SQL知识大全三):SQL中的字符串处理和条件查询

SQL知识大全（四):SQL中的数据表关联

分享数据知识，成就数据理想

日期相关的问题与JavaScript一样古老。从理论上讲，如果不是因为API的许多弱点，可以使用JavaScript的date对象执行日期计算。幸运的是，有一些有用的库可以为我们节省很多工作。其中之一是date-fns。

一个问题，例如，是与日期对象不同的时区的处理，由于JavaScript使用当前系统时区为基础。这可能会导致困难，尤其是涉及跨多个时区的应用程序时。月份的表示形式是JavaScript中date对象的另一特性。例如，一月的值指定为0。但是，当涉及到日期和年份时，JavaScript会再次遵循预期的标准，因此，5 号用数字5表示。

当您实现一个使用日期值的应用程序时，您经常会发现必须创建，修改和输出它们的问题。使用机载JavaScript工具，可以轻松进行创建和输出。但是，如果日期被修改，例如，如果你想从一个日期减去两天了，这是不再可能。当然，您可以获取日期的时间戳，然后减去相应的毫秒数以达到目标日期。该解决方案不容易阅读和维护，或者特别优雅。由于这个问题，还有更多的问题，过去已经创建了许多库，以使您更轻松地处理JavaScript中的日期值。市场上最广泛的解决方案之一是Moment.js。不过，前一阵子已经成为了一个严重的竞争对手：

date-fns与Moment.js有何不同？

最重要的区别之一是项目名称，因为fns代表功能。日期FNS的功能，它允许您使用日期值的集合。
与此相反，Moment.js具有面向对象的方法。在这里，您创建一个瞬间 -instance和工作与此对象的方法。当然，这会影响包装尺寸。
Moment.js默认包含整个界面。您确实可以优化程序包，但这需要其他步骤。在date-fns中，您仅加载真正需要的功能。
不过，在带有Node.js的后端应用程序中，这无关紧要，因为软件包大小是一个小问题。您可以使用日期FNS就像Moment.js，在前端的浏览器。包装的大小在这里起决定性作用。

date-fns的开发人员不仅确保将项目划分为许多小的且很大程度上独立的功能，而且还确保这些功能是纯功能。例如，您传递一个日期对象和要添加到addHours函数的小时数。结果，您将获得一个新的日期对象，其中指定的小时数晚于您输入的日期。因此，没有副作用，例如直接修改输入。

如何安装日期FNS？

与大多数其他JavaScript库一样，date-fns可作为npm软件包提供，并可通过npm进行安装。在项目中使用命令npm install date-fns进行操作。该软件包将作为依赖项自动添加到您的package.json文件中。同样，您可以将纱线与纱线 添加日期-fns命令一起使用。

如何使用它？

您可以使用日期FNS包与CommonJS的模块系统都和也与ES模块。在下面的示例中，您使用格式函数输出当前日期。清单1显示了如何使用CommonJS模块系统。

清单1：通过CommonJS模块化系统集成date-fns

1个

2

3

4

5

const { format } = require('date-fns');

const date = new Date();

console.log(`Heute ist der: ${format(date, 'DD.MM.YYYY')}`);

Node.js的较新版本还支持关键字的进口和出口，以进口和出口分别模块。此时，您可以导入整个date-fns软件包并访问所需的功能，也可以利用每个功能在单独的文件中可用的事实，因此可以单独导入格式功能。你可以看到这是如何工作清单2所示。

清单2：将date-fns与ES模块一起使用

1个

2

3

import { format } from 'date-fns/format';

const date = new Date();

console.log(`Heute ist der: ${format(date, 'DD.MM.YYYY')}`);

格式化日期值

使用格式，您已经学习了格式化日期值的最重要功能。
您可以使用格式字符串来指定要格式化日期的哪一部分以及如何格式化。
您可以在https://date-fns.org/docs/format中找到可以在格式字符串中使用的各个令牌的全面参考。

除此功能外，您还可以访问其他辅助功能，例如distanceInWords函数，该函数以可读形式输出两个日期值之间的差。

日期算术

已经提到的JavaScript中对象日期的漏洞是缺少对日期算术的支持。因此，事不宜迟地执行加法或减法。
date-fns为此提供了许多辅助功能。这些功能通常有一个统一的命名方案：首先，你指定的操作，其次是要与工作单位。
这将导致函数名称，例如addMinutes或subYears。此类别的所有函数都将日期对象作为第一个参数，将数字作为第二个参数表示要添加或减去的单位数。例如，在一个小时的四分之三添加到当前为止，你可以从清单3使用的代码。

清单3：使用date-fns的日期算术

1个

2

3

4

5

const { addMinutes, addHours, format } = require('date-fns');

const date = addMinutes(addHours(new Date(), 1), 45);

console.log(format(date, 'DD.MM.YYYY HH:mm'));

比较

日期FNS的比较功能也非常有帮助，在他们的帮助，你可以决定是否一个谎言日期之前或之后另一个，还是在某个日期在于未来或过去。清单4使用isAfter和isFuture函数作为示例来说明它们的用法。

清单4：wit dat-fns的比较

1个

2

3

4

5

6

const { isAfter, isFuture, addHours } = require('date-fns');

const date1 = new Date();

const date2 = addHours(new Date(), 5);

console.log(`Date1 is ${isAfter(date1, date2) ? 'after' : 'before'} Date2`);

console.log(`Date2 is ${isFuture(date2) ? 'not' : ''} in the past`);

进一步的操作

该日期FNS包为您提供不仅是简单的操作，如加，但也更复杂的操作，如areRangesOverlapping功能，你可以用它来确定两个时间跨度是否重叠。

随着最小和最大的功能，你可以找到一系列的日期值的最早或最晚日期。

借助compareAsc和compareDsc函数，您还可以对具有日期值的数组进行排序。该函数作为比较函数传递给数组的sort方法。清单5是这样的一个例子。

清单5：对日期值进行排序

1个

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

const { compareAsc } = require('date-fns');

const sortedDates = [

new Date(2001, 1, 1),

new Date(2003, 3, 3),

new Date(2002, 2, 2),

].sort(compareAsc);

console.log(sortedDates);

结论

Moment.js或date-fns之类的许多程序包都提供了，您也可以使用本机JavaScript来实现。但是，在这些情况下，源代码的可读性受到很大影响。除了更正JavaScript 日期对象的特性之外，这是支持使用这些库的最重要的论据之一。

这里显示的日期FNS的可能性仅代表库中的一小部分，并只给你这个库的功能范围的味道。有了众多扩展，并为应用程序的国际化提供了很好的支持，您下次确定一个应用程序的日期库时，至少应将date-fns入围。

最后，开发这么多年我也总结了一套学习Java的资料与面试题，如果你在技术上面想提升自己的话，可以关注我，私信发送领取资料或者在评论区留下自己的联系方式，有时间记得帮我点下转发让跟多的人看到哦。

我们的日常开发工作中，经常会遇到需要计算两个日期之间差异的情况，比如统计项目的开发周期、计算用户的订阅时间等。那么，如何在JavaScript中优雅地实现这个功能呢？

业务需求

假设你是一名初级前端开发工程师，正在负责一个项目的进度管理系统。项目经理要求你计算每个项目的开发周期，并以月为单位展示在系统中。为了实现这个功能，你需要计算两个日期之间的月份差异。接下来，我们就来看一下如何实现吧！

实现步骤

首先，我们可以通过计算两个日期之间的年份差异，并将其转换为月份差异。然后减去起始日期的月份，再加上结束日期的月份，最终得到两个日期之间的月份差异。

const monthDiff = (d1, d2) => {
  let months;
  months = (d2.getFullYear() - d1.getFullYear()) * 12;
  months -= d1.getMonth();
  months += d2.getMonth();
  return months <= 0 ? 0 : months;
}

const start = new Date(2020, 1, 1); // 项目开始日期
const end = new Date(2020, 5, 1); // 项目结束日期
console.log(monthDiff(start, end)); // 输出4

代码解析

1. 计算年份差异并转换为月份：首先，我们计算两个日期之间的年份差异，并将其乘以12，转换为月份。

months = (d2.getFullYear() - d1.getFullYear()) * 12;

2. 减去起始日期的月份：接着，我们减去起始日期的月份。

months -= d1.getMonth();

3. 加上结束日期的月份：然后，再加上结束日期的月份。

months += d2.getMonth();

4. 返回月份差异：最后，我们判断计算出来的月份差异是否小于等于0，如果是则返回0，否则返回实际的月份差异。

return months <= 0 ? 0 : months;

结束

在实际工作中，这个小技巧可以帮助我们快速计算项目的开发周期。例如，当项目经理询问某个项目的开发周期时，我们只需输入项目的开始和结束日期，就能快速得出结果，提升工作效率。