Mysql日期格式以及内置日期函数用法详解
作者:ximeneschen 发布时间:2024-01-24 22:39:11
一、MySQL中的日期时间类型
MySQL中常用的几种时间类型有:date、datetime、time、year、timestamp
datetime和timestamp的区别
相同点: 存储格式相同 datetime和timestamp两者的时间格式都是YYYY-MM-DD HH:MM:SS
不同点:
a. 存储范围不同. datetime的范围是1000-01-01到9999-12-31. 而timestamp是从1970-01-01到2038-01-19, 即后者的时间范围很小.
b. 与时区关系. datetime是存储服务器当前的时区. 而timestamp类型,是将服务器当前时间转换为UTC(世界时间)来存储.即datetime与时区无关,存什么,返回什么. 而timestamp存储的时间,返回的时间会随着数据库的时区不同而发生改变.
详细解释
datetime : 时间日期型,格式是YYYY-mm-dd HH:ii:ss,表示的范围是从1000到9999。但是有零值,0000-00-00 00:00:00;
date:日期,就是datetime中的date部分;
time:时间(段),指定的某个区间之间,从-时间到+时间(有负时间表示);
timestamp:时间戳,并不是常规意义时间戳(如:14253685),范围是’1970-01-01 00:00:00’到2037年。格式为YYYY-mm-dd HH:ii:ss,格式与datetime完全一致;
year:yy和yyyy,yyyy的范围是1901-2155,yy的范围是1970-2069。
两位year(00-69表示2000-2069,70-99表示1970~1999)。当应用只需要记录年份时,year比date更省空间
create table my_date(
d1 datetime,
d2 date,
d3 time,
d4 timestamp,
d5 year
)charset utf8;
描述表:
测试表
insert into my_date VALUES('2015-09-28 11:50:36','2015-09-28','11:50:54','2015-09-28 11:51:08','2015');
insert into my_date VALUES('2015-09-28 11:50:36','2015-09-28','-11:50:54','2015-09-28 11:51:08','2015');-- -11
insert into my_date VALUES('2015-09-28 11:50:36','2015-09-28','-211:50:54','2015-09-28 11:51:08','2015');-- -2 11
insert into my_date VALUES('2015-09-28 11:50:36','2015-09-28','-2 11:50:54','2015-09-28 11:51:08','2015');-- -2过去两天
#year用69标识-2069
insert into my_date VALUES('2015-09-28 11:50:36','2015-09-28','11:50:54','2015-09-28 11:51:08','69');-- 69
#year用70标识-1970
insert into my_date VALUES('2015-09-28 11:50:36','2015-09-28','11:50:54','2015-09-28 11:51:08','70');-- 70
insert into my_date VALUES(now(),now(),now(),now(),now());-- 70
二、mysql中获取时间戳
select UNIX_TIMESTAMP();
三、mysql中datetime类型的长度位数
通常我们MySQL中设计datetime类型长度都默认为0:这时插入时间通常会是我们常见到的:2020-08-29 12:52:16格式。但是如果datetime(n)中的n不为0呢?
create table my_date(
datetime datetime,
datetime2 datetime(2),
date date,
time time,
timestamp timestamp,
year year
)charset utf8;
四、mysql常用时间函数
获取当前时间
now()
sysdate()
current_timestamp()
sysdate() 日期函数和 now() 类似, 不同之处在于: now() 在执行开始的时候就得到值了, sysdate() 在函数执行时动态得到的值,例子如下:
时间转换函数(字符串转时间,时间转字符串) 日期/时间转字符串函数: date_format(date, format)(可以格式日期和时间), time_format(time, format)(可以格式时间)
字符串转换日期 函数: str_to_date(str, format)
可以看出, str_to_date(str, format) 转换函数, 可以把一些杂乱无章的字符串转换为日期格式
.
日期, 天数转换函数: to_days(date), from_days(days)
,to_days():返回的是一个天数!从年份 0 开始的天数,from_days(days)作用与to_days相反。
时间, 秒 转换函数: time_to_sec(time), sec_to_time(seconds)
两个函数是相互的
设定日期, 时间函数: makedate(year, dayfoyear), maketime(hour, minute, second)
时间戳, 日期转换函数: unix_timestamp(), unix_timestamp(date), from_unixtime(unix_timestamp), from_unixtime(unix_timestamp, format)
# 当前时间戳
select unix_timestamp();
# 指定时间的时间戳
select unix_timestamp('2020-09-09 12:00:00');
# 时间戳转换成日期
select from_unixtime(1599624000);
# 时间戳转换成指定格式日期
select from_unixtime(1599624000, '%Y-%D-%M %h:%i:%s');
五、日期时间计算函数
日期增加一个时间间隔函数: date_add()
set @dt = now();
# 增加一天
select date_add(@dt, interval 1 day);
# 增加一小时
select date_add(@dt, interval 1 hour);
# 增加一周
select date_add(@dt, interval 1 week);
# 增加一季度
select date_add(@dt, interval 1 quarter);
# 增加一天
select date_add(@dt, interval -1 day);
为指定日期减去一个时间间隔函数: date_sub()
,用法如上。
两个日期, 时间相减函数: datediff(date1, date2), timediff(time1, time2)
时间戳 (timestamp) 转换, 增加, 减少函数: timestamp(date), timestamp(dt, time), timestampadd(unit, interval, datetime_expr), timestampdiff(unit, datetime_expr1, datetime_expr2)
# timestamp(date)
mysql> select timestamp('2020-09-09');
+-------------------------+
| timestamp('2020-09-09') |
+-------------------------+
| 2020-09-09 00:00:00 |
+-------------------------+
1 row in set (0.00 sec)
# timestamp(dt, time)
mysql> select timestamp('2020-09-09 09:00:00', '01:00:00');
+----------------------------------------------+
| timestamp('2020-09-09 09:00:00', '01:00:00') |
+----------------------------------------------+
| 2020-09-09 10:00:00 |
+----------------------------------------------+
1 row in set (0.00 sec)
# timestampadd(unit, interval, datetime_expr)
mysql> select timestampadd(day, 1, '2020-09-09 09:00:00');
+---------------------------------------------+
| timestampadd(day, 1, '2020-09-09 09:00:00') |
+---------------------------------------------+
| 2020-09-10 09:00:00 |
+---------------------------------------------+
1 row in set (0.00 sec)
# timestampdiff(unit, datetime_expr1, datetime_expr2), 返回结果以天为单位
mysql> select timestampdiff(day, '2020-09-09', '2020-09-01');
+------------------------------------------------+
| timestampdiff(day, '2020-09-09', '2020-09-01') |
+------------------------------------------------+
| -8 |
+------------------------------------------------+
1 row in set (0.00 sec)
# 返回结果以小时为单位
mysql> select timestampdiff(hour, '2020-09-09 09:00:00', '2020-09-01 08:00:00');
+-------------------------------------------------------------------+
| timestampdiff(hour, '2020-09-09 09:00:00', '2020-09-01 08:00:00') |
+-------------------------------------------------------------------+
| -193 |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2020-09-09', '2020-09-01');
+--------------------------------------+
| datediff('2020-09-09', '2020-09-01') |
+--------------------------------------+
| 8 |
+--------------------------------------+
1 row in set (0.00 sec)
timestampdiff() 函数就比 datediff() 功能强多了,datediff() 只能计算两个日期(date)之间相差的天数
来源:https://blog.csdn.net/cristianoxm/article/details/127026692
猜你喜欢
- 使用散点图矩阵图,可以两两发现特征之间的联系pd.plotting.scatter_matrix(frame, alpha=0.5, c,f
- Flask框架是Python开发的一个基于Werkzeug和Jinja 2的web开发微框架,它的优势就是极其简洁, 但又非常灵活,而且容易
- 本文实例讲述了Python找出list中最常出现元素的方法。分享给大家供大家参考,具体如下:假设一个list中保存着各种元素,需要统计每个元
- MYSQL里的BLOB数据类型BLOB是一个二进制大对象,用来存储可变数量的数据。BLOB类型分为4种:TinyBlob、Blob、Medi
- 本文实例讲述了Python 私有化操作。分享给大家供大家参考,具体如下:私有化xx: 公有变量_x: 单前置下划线,私有化属性或方法,fro
- 本人刚开始学习python,看了一段时间视频教程之后,决定做一个小游戏来巩固一下知识点,就做了一个文字版飞行棋,暂不具备图形界面。把代码贴出
- 前言最近碰到了照片识别的场景,正好使用了face_recognition项目,给大家分享分享。face_recognition项目能做的很多
- Problem:Solution:参考stackoverflow给出的解决方案:https://stackoverflow.com/ques
- 一简介本软件作用于人员管理,1创建一个岗位管理界面,点击岗位管理之后,设置好岗位名称,拖动鼠标画框,完成岗位创建,之后里面可以放置人员。可以
- 1.什么是接口接口就是一种规范与标准,在生活中经常见接口,例如:笔记本电脑的USB接口,可以将任何厂商生产的鼠标与键盘,与电脑进行链接。为什
- 正常情况下,当我们定义了一个class,创建了一个class的实例后,我们可以给该实例绑定任何属性和方法,这就是动态语言的灵活性。先定义cl
- Paramiko 是由 Python 语言编写的一个扩展模块,提供了基于 SSHv2 协议 (包括客户端和服务端)的多种功能实现。通常被用来
- 前言其实Beautiful Soup 模块除了能够搜索和导航之外,还能够修改 HTML/XML 文档的内容。这就意味着能够添加或删除标签、修
- python随机数种子seed()栗子1import numpy as npimport randomrandom.seed(0)np.ra
- 一. 元组元组是Python中的一个内置的数据结构,它是一个不可变的序列,所谓的不可变序列就是不可以进行增删改的操作。1.1 元组的创建元组
- jQuery居然都没有JSON的decode和encode,精确类型判断也没有,囧……自己动手写吧!不过这些东西在网上都已经有很好的版本了,
- 很多年前,我们就可以轻易的从很多国营商场、火车车厢、饭馆旅馆中看到墙上挂的那个小本本-意见薄,作为经营方与顾客沟通的
- 一、Flask简介Flask 是一个 Python 实现的 Web 开发微框架。官网:http://flask.pocoo.org/二、De
- python入门细节相除后的类型type(2/2)floattype(2//2)int双斜杠是整除,出来的类型是int。单斜杠的出来的是fl
- 总结为:改注册表。顺手写个脚本:import tkinter as tkfrom tkinter import ttkimport winr