最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

SQL知识大全5:SQL中的时间函数

XAMPP案例 admin 56浏览 0评论

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

zzzzzu00000005

常用的时间函数

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

转载请注明:XAMPP中文组官网 » SQL知识大全5:SQL中的时间函数