Mysql日期格式以及内置日期函数用法详解

作者:ximeneschen 时间:2024-01-24 22:39:11 

一、MySQL中的日期时间类型

MySQL中常用的几种时间类型有:date、datetime、time、year、timestamp

Mysql日期格式以及内置日期函数用法详解

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;

描述表:

Mysql日期格式以及内置日期函数用法详解

测试表

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日期格式以及内置日期函数用法详解

二、mysql中获取时间戳

select UNIX_TIMESTAMP();

Mysql日期格式以及内置日期函数用法详解

三、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日期格式以及内置日期函数用法详解

四、mysql常用时间函数

获取当前时间

  • now()

  • sysdate()

  • current_timestamp()

Mysql日期格式以及内置日期函数用法详解

sysdate() 日期函数和 now() 类似, 不同之处在于: now() 在执行开始的时候就得到值了, sysdate() 在函数执行时动态得到的值,例子如下:

Mysql日期格式以及内置日期函数用法详解

时间转换函数(字符串转时间,时间转字符串) 日期/时间转字符串函数: date_format(date, format)(可以格式日期和时间), time_format(time, format)(可以格式时间)

Mysql日期格式以及内置日期函数用法详解

Mysql日期格式以及内置日期函数用法详解

字符串转换日期 函数: str_to_date(str, format)

Mysql日期格式以及内置日期函数用法详解

可以看出, str_to_date(str, format) 转换函数, 可以把一些杂乱无章的字符串转换为日期格式.

日期, 天数转换函数: to_days(date), from_days(days),to_days():返回的是一个天数!从年份 0 开始的天数,from_days(days)作用与to_days相反。

Mysql日期格式以及内置日期函数用法详解

时间, 秒 转换函数: time_to_sec(time), sec_to_time(seconds)

Mysql日期格式以及内置日期函数用法详解

两个函数是相互的

设定日期, 时间函数: makedate(year, dayfoyear), maketime(hour, minute, second)

Mysql日期格式以及内置日期函数用法详解

时间戳, 日期转换函数: 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');

Mysql日期格式以及内置日期函数用法详解

五、日期时间计算函数

日期增加一个时间间隔函数: 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)

Mysql日期格式以及内置日期函数用法详解

时间戳 (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

标签:mysql,日期,时间
0
投稿

猜你喜欢

  • mysql5.x升级到mysql5.7后导入之前数据库date出错的快速解决方法

    2024-01-22 10:09:17
  • python调用dll出现精度问题解决

    2022-10-30 06:59:48
  • 解析python 类方法、对象方法、静态方法

    2022-10-08 04:47:57
  • Python设计密码强度校验程序

    2022-09-29 08:37:12
  • js省市联动效果完整实例代码

    2024-04-18 10:15:34
  • Flask模拟实现CSRF攻击的方法

    2023-11-18 16:21:39
  • python的几种开发工具介绍

    2021-09-28 01:54:44
  • 土豆网前端概况

    2008-01-24 12:06:00
  • 关于“简单,可依赖”

    2008-10-22 13:33:00
  • 教你怎么用Python实现自动生日祝福

    2022-04-08 01:03:25
  • 如何改良你的CSS代码编写结构

    2008-09-29 16:03:00
  • 基于go-cqhttp与Flask搭建定制机器人项目实战示例

    2024-04-26 17:30:32
  • Python实现小数转化为百分数的格式化输出方法示例

    2023-07-15 05:58:15
  • java使用dbcp2数据库连接池

    2024-01-29 05:33:42
  • 深入浅出Golang中的sync.Pool

    2024-02-11 12:59:34
  • 快速解决js动态改变dom元素属性后页面及时渲染的问题

    2024-05-02 16:17:39
  • MySQL 5.6.36 Windows x64位版本的安装教程详解

    2024-01-25 09:31:44
  • Python实现四个经典小游戏合集

    2021-08-16 12:17:35
  • python和shell监控linux服务器的详细代码

    2021-11-09 06:23:22
  • Python去除列表中重复元素的方法

    2021-06-23 20:44:27
  • asp之家 网络编程 m.aspxhome.com