c mysql timestamp_【Mysql】Datetime和Timestamp区别,及mysql中各种时间的使用

说到数据库时间类型,大部分同学都会想到date、datetime、timestamp之类的。

我之前在项目遇到一个问题,测试同事在测试时,由于会测试205几年的数据,在入库时会抛出数据库异常,原因就是timestamp是有最大年份限制的。

下面先说说datetime与timestamp的区别:

datetime的默认值为null,timestamp的默认值不为null,且为系统当前时间(current_timestatmp)。如果不做特殊处理,且update没有指定该列更新,则默认更新为当前时间。

datetime占用8个字节,timestamp占用4个字节。timestamp利用率更高。

二者存储方式不一样,对于timestamp,它把客户端插入的时间从当前时区转化为世界标准时间(UTC)进行存储,查询时,逆向返回。但对于datetime,基本上存什么是什么。

二者范围不一样。timestamp范围:‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’; datetime范围:’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。原因是,timestamp占用4字节,能表示最大的时间毫秒为2的31次方减1,也就是2147483647,换成时间刚好是2038-01-19 03:14:07.999999。

Mysql中常用到的时间相关的函数:

常用函数

6dbea6f00eecfc5c561e4c279908554b.png

Mysql服务器的三种时区设置

系统时区 - 保存在系统变量system_time_zone

服务器时区 - 保存在全局系统变量global.time_zone

每个客户端连接的时区 - 保存在会话变量session.time_zone

注意:客户端时区,会影响一些日期函数返回值的显示,例如:now()、curtime()、curdate(),也影响timestamp列值的显示。

默认情况下,客户端时区和服务器时区相同,均为SYSTEM,表示使用系统时区。

函数特殊说明

1、NOW([fsp]):返回服务器的当前日期和时间(fsp指定小数秒的精度,取值0--6)

now()函数的同义词有:CURRENT_TIMESTAMP 、 CURRENT_TIMESTAMP()、LOCALTIMESTAMP 、 LOCALTIMESTAMP()、LOCALTIME 、 LOCALTIME()。

2、SYSDATE( ):返回服务器的当前日期和时间。

与now()不同之处,now()返回语句执行时间,sysdate()返回函数执行时间。

3、CURTIME([fsp]):返回当前时间,只包含时分秒(fsp指定小数秒的精度,取值0--6)

同义词有:CURRENT_TIME 、 CURRENT_TIME()

4、CURDATE():返回当前日期,只包含年月日

同义词有: CURRENT_DATE 、CURRENT_DATE()

5、TIMEDIFF(expr1, expr2):返回两个日期相减(expr1 − expr2 )相差的时间数(两个参数类型必须相同)

mysql> select timediff('18:32:59','60000');

+------------------------------+

| timediff('18:32:59','60000') |

+------------------------------+

| 12:32:59 |

+------------------------------+

mysql> select timediff('18:32:59','2017-1-1 60000');

+---------------------------------------+

| timediff('18:32:59','2017-1-1 60000') |

+---------------------------------------+

| NULL |

+---------------------------------------+

DATEDIFF(expr1, expr2):返回两个日期相减(expr1 − expr2 )相差的天数。

mysql> select datediff('2017-3-24 18:32:59','2016-9-1');

+-------------------------------------------+

| datediff('2017-3-24 18:32:59','2016-9-1') |

+-------------------------------------------+

| 204 |

+-------------------------------------------+

6、日期时间运算函数:分别为给定的日期date加上(add)或减去(sub)一个时间间隔值expr

DATE_ADD(date, INTERVAL  expr  unit);

DATE_SUB(date, INTERVAL  expr  unit);

interval是间隔类型关键字

expr是一个表达式,对应后面的类型

unit是时间间隔的单位(间隔类型)(20个)

hour - 小时;minute - 分;second - 秒;microsecond - 毫秒; year - 年;month - 月;

day - 日;  week - 周; quarter - 季; year_month - 年和月;day_hour - 日和小时;

day_minute - 日和分钟;day_second - 日和秒;hour_minute - 小时和分;hour_second - 小时和秒;

minute_second - 分钟和秒。

mysql> select now(),date_add(now(),interval 1day);  #加一天+---------------------+--------------------------------+

| now() | date_add(now(),interval 1 day) |

+---------------------+--------------------------------+

| 2017-03-24 14:53:08 | 2017-03-25 14:53:08 |

+---------------------+--------------------------------+mysql> SELECT date_sub('2005-01-01 00:00:00',INTERVAL '1 1:1:1'DAY_SECOND);  #减1天1小时1分1秒+---------------------------------------------------------------+

| date_sub('2005-01-01 00:00:00',INTERVAL '1 1:1:1' DAY_SECOND) |

+---------------------------------------------------------------+

| 2004-12-30 22:58:59 |

+---------------------------------------------------------------+

7、选取日期时间的各个部分:日期、时间、年、季度、月、日、小时、分钟、秒、微秒(常用)

SELECT now(),date(now()); -- 日期

SELECT now(),time(now()); -- 时间

SELECT now(),year(now()); -- 年

SELECT now(),quarter(now()); -- 季度

SELECT now(),month(now()); -- 月

SELECT now(),week(now()); -- 周

SELECT now(),day(now()); -- 日

SELECT now(),hour(now()); -- 小时

SELECT now(),minute(now()); -- 分钟

SELECT now(),second(now()); -- 秒

SELECT now(),microsecond(now()); -- 微秒

EXTRACT(unit  FROM  date):从日期中抽取出某个单独的部分或组合

SELECT now(),extract(YEAR FROM now()); -- 年

SELECT now(),extract(QUARTER FROM now()); -- 季度

SELECT now(),extract(MONTH FROM now()); -- 月

SELECT now(),extract(WEEK FROM now()); -- 周

SELECT now(),extract(DAY FROM now()); -- 日

SELECT now(),extract(HOUR FROM now()); -- 小时

SELECT now(),extract(MINUTE FROM now()); -- 分钟

SELECT now(),extract(SECOND FROM now()); -- 秒

SELECT now(),extract(YEAR_MONTH FROM now()); -- 年月

SELECT now(),extract(HOUR_MINUTE FROM now()); -- 时分

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值