MySQL的10位或13位时间戳获取，表示及13位时间戳的存储

MySQL的10位或13位时间戳获取，表示及13位时间戳的存储

一、毫秒、微秒名词解释：

毫秒：millisecond -- 千分之一秒 微秒：microsecond -- 一百万分之一秒 1 秒 = 1000 毫秒；1 毫秒 = 1000 微秒

10位时间戳的单位是秒

13位时间戳的单位是毫秒

下面首先给出结论，

13位时间戳存储要么存为bigint，要么存为varchar(13)类型。不能使用int，因为“13位时间戳只能存bigint ，因为13位时间戳超出了int的范围”。

13位时间戳只能存bigint ，因为13位时间戳超出了int的范围。

二、mysql中int、bigint、smallint 和 tinyint的区别详细介绍

1 bytes = 8 bit ,一个字节最多可以代表的数据长度是2的8次方 11111111 在计算机中也就是

-128到127

1、BIT[M]

位字段类型，M表示每个值的位数，范围从1到64，如果M被忽略，默认为1

2、TINYINT[(M)] [UNSIGNED] [ZEROFILL]  M默认为4

很小的整数。带符号的范围是-128到127。无符号的范围是0到255。

3、 BOOL，BOOLEAN

是TINYINT(1)的同义词。zero值被视为假。非zero值视为真。

4、SMALLINT[(M)] [UNSIGNED] [ZEROFILL] M默认为6

小的整数。带符号的范围是-32768到32767。无符号的范围是0到65535。

5、MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] M默认为9

中等大小的整数。带符号的范围是-8388608到8388607。无符号的范围是0到16777215。

6、 INT[(M)] [UNSIGNED] [ZEROFILL]   M默认为11

普通大小的整数。带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295。

7、BIGINT[(M)] [UNSIGNED] [ZEROFILL] M默认为20

大整数。带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615。

注意：这里的M代表的并不是存储在数据库中的具体的长度，以前总是会误以为int(3)只能存储3个长度的数字，int(11)就会存储11个长度的数字，这是大错特错的。

tinyint(1) 和 tinyint(4) 中的1和4并不表示存储长度，只有字段指定zerofill是有用，

如tinyint(4)，如果实际值是2，如果列指定了zerofill，查询结果就是0002，左边用0来填充。

8、char是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：

  char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．

（在检索操作中那些填补出来的空格字符将被去掉）

在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）．

三、在MySQL中用来判断是否需要进行对据列类型转换的规则

  1、在一个数据表里，如果每一个数据列的长度都是固定的，那么每一个数据行的长度也将是固定的．

  2、只要数据表里有一个数据列的长度的可变的，那么各数据行的长度都是可变的．

  3、如果某个数据表里的数据行的长度是可变的，那么，为了节约存储空间，MySQL会把这个数据表里的固定长度类型的数据列转换为相应的可变长度类型．

例外：长度小于4个字符的char数据列不会被转换为varchar类型

参考：https://www.cnblogs.com/yiwd/p/5531167.html

四、Mysql存储日期类型用int、timestamp还是datetime?区别及使用

（1）通常存储时间用datetime类型，现在很多系统也用int存储时间，它们有什么区别？个人更喜欢使用int这样对于日期计算时比较好，下面我们一起来看到底那种会好些。

1、int

   1）4个字节存储，INT的长度是4个字节，存储空间上比datatime少，int索引存储空间也相对较小，排序和查询效率相对较高一点

   2）可读性极差，无法直观的看到数据，可能让你很恼火

2、TIMESTAMP

   1）4个字节储存

   2）值以UTC格式保存

   3）时区转化 ，存储时对当前的时区进行转换，检索时再转换回当前的时区。

   4）TIMESTAMP值不能早于1970或晚于2037

3、datetime

   1）8个字节储存

   2）与时区无关
   3）以'YYYY-MM-DD HH:MM:SS'格式检索和显示DATETIME值。支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'

（2）、MySQL中如何表示当前时间？

其实，表达方式还是蛮多的，汇总如下：

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

NOW()

LOCALTIME

LOCALTIME()

LOCALTIMESTAMP

LOCALTIMESTAMP()

示例如下：

mysql> select CURRENT_TIMESTAMP;

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

| CURRENT_TIMESTAMP |

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

| 2018-10-19 17:44:18 |

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

1 row in set (0.00 sec)

mysql> select CURRENT_TIMESTAMP();

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

| CURRENT_TIMESTAMP() |

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

| 2018-10-19 17:44:25 |

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

1 row in set (0.00 sec)

mysql> select NOW();

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

| NOW() |

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

| 2018-10-19 17:44:34 |

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

1 row in set (0.00 sec)

mysql> select LOCALTIME;

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

| LOCALTIME |

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

| 2018-10-19 17:44:42 |

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

1 row in set (0.00 sec)

mysql> select LOCALTIME();

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

| LOCALTIME() |

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

| 2018-10-19 17:44:50 |

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

1 row in set (0.00 sec)

mysql> select LOCALTIMESTAMP;

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

| LOCALTIMESTAMP |

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

| 2018-10-19 17:44:57 |

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

1 row in set (0.00 sec)

mysql> select LOCALTIMESTAMP();

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

| LOCALTIMESTAMP() |

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

| 2018-10-19 17:45:04 |

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

1 row in set (0.00 sec)

（3）、关于TIMESTAMP和DATETIME的比较

一个完整的日期格式如下：YYYY-MM-DD HH:MM:SS[.fraction]，它可分为两部分：date部分和time部分，其中，date部分对应格式中的“YYYY-MM-DD”，time部分对应格式中的“HH:MM:SS[.fraction]”。对于date字段来说，它只支持date部分，如果插入了time部分的内容，它会丢弃掉该部分的内容，并提示一个warning。

如下所示：

mysql> create table test(id int,hiredate date);

Query OK, 0 rows affected (0.05 sec)

mysql> delete from test;

Query OK, 2 rows affected (0.01 sec)

mysql> insert into test values(1,'20151208000000');

Query OK, 1 row affected (0.00 sec) # 注：第一个没提示warning的原因在于它的time部分都是0

mysql> insert into test values(1,'20151208104400');

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;

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

| Level | Code | Message |

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

| Note | 1292 | Incorrect date value: '20151208104400' for column 'hiredate' at row 1 |

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

1 row in set (0.00 sec)

mysql> select * from test;

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

| id | hiredate |

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

| 1 | 2015-12-08 |

| 1 | 2015-12-08 |

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

2 rows in set (0.00 sec)

1）、TIMESTAMP和DATETIME的相同点：

     1> 两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。

2）、TIMESTAMP和DATETIME的不同点：

     1> 两者所能存储的时间范围不一样

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'。

     1> 两者的存储方式不一样

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。

而对于DATETIME，不做任何改变，基本上是原样输入和输出。

总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。当然，对于跨时区的业务，TIMESTAMP更为合适。

3)、下面，我们来验证一下

首先创建两种测试表，一个使用timestamp格式，一个使用datetime格式。

mysql> create table test1(id int,hiredate datetime);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into test1 values(1,'20151208000000');

Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;

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

| id | hiredate |

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

| 1 | 2015-12-08 00:00:00 |

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

1 row in set (0.00 sec)

mysql> create table test2(id int,hiredate timestamp);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into test2 values(1,'20151208000000');

Query OK, 1 row affected (0.01 sec)

mysql> select * from test2;

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

| id | hiredate |

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

| 1 | 2015-12-08 00:00:00 |

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

1 row in set (0.00 sec)

两者输出是一样的。

其次修改当前会话的时区

mysql> show variables like '%time_zone%';

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

| Variable_name | Value |

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

| system_time_zone | CST |

| time_zone | SYSTEM |

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

2 rows in set (0.03 sec)

mysql> set time_zone='+0:00';

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test2;

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

| id | hiredate |

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

| 1 | 2015-12-07 16:00:00 |

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

1 row in set (0.00 sec)

mysql> select * from test1;

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

| id | hiredate |

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

| 1 | 2015-12-08 00:00:00 |

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

1 row in set (0.00 sec)

上述“CST”指的是MySQL所在主机的系统时间，是中国标准时间的缩写，China Standard Time UT+8:00

通过结果可以看出，test1中返回的时间提前了8个小时，而test2中时间则不变。这充分验证了两者的区别。

五、关于TIMESTAMP和DATETIME的自动初始化和更新

首先，我们先看一下下面的操作

mysql> create table test4(id int,hiredate timestamp);

Query OK, 0 rows affected (0.03 sec)

mysql> insert into test4(id) values(1);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test4;

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

| id | hiredate |

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

| 1 | 2018-10-19 09:54:55 |

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

1 row in set (0.00 sec)

mysql> set time_zone='+8:00';

Query OK, 0 rows affected (0.00 sec)

mysql> select * from test4;

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

| id | hiredate |

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

| 1 | 2018-10-19 17:54:55 |

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

1 row in set (0.00 sec)

mysql> show create table test4\G;

*************************** 1. row ***************************

Table: test4

Create Table: CREATE TABLE `test4` (

`id` int(11) DEFAULT NULL,

`hiredate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

看起来是不是有点奇怪，我并没有对hiredate字段进行插入操作，它的值自动修改为当前值，而且在创建表的时候，我也并没有定义“show create table test\G”结果中显示的“ DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”。

其实，这个特性是自动初始化和自动更新（Automatic Initialization and Updating）。

自动初始化指的是如果对该字段（譬如上例中的hiredate字段）没有显性赋值，则自动设置为当前系统时间。

自动更新指的是如果修改了其它字段，则该字段的值将自动更新为当前系统时间。

它与“explicit_defaults_for_timestamp”参数有关。

默认情况下，该参数的值为OFF，如下所示：

mysql> show variables like '%explicit_defaults_for_timestamp%';

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

| Variable_name | Value |

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

| explicit_defaults_for_timestamp | OFF |

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

1 row in set (0.01 sec)

下面我们看看官档的说明：

By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly。

很多时候，这并不是我们想要的，如何禁用呢？

1. 将“explicit_defaults_for_timestamp”的值设置为ON。

2. “explicit_defaults_for_timestamp”的值依旧是OFF，也有两种方法可以禁用

     1> 用DEFAULT子句该该列指定一个默认值

     2> 为该列指定NULL属性。

mysql> create table test6(id int,hiredate timestamp null) ;

mysql> show create table test6 \G;

*************************** 1. row ***************************

Table: test6

Create Table: CREATE TABLE `test6` (

`id` int(11) DEFAULT NULL,

`hiredate` timestamp NULL DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

mysql> create table test7(id int,hiredate timestamp default 0);

ERROR 1067 (42000): Invalid default value for 'hiredate'

mysql> create table test7(id int,hiredate timestamp default '2018-10-11 00:00:00');

Query OK, 0 rows affected (0.03 sec)

mysql> show create table test7 \G;

*************************** 1. row ***************************

Table: test7

Create Table: CREATE TABLE `test7` (

`id` int(11) DEFAULT NULL,

`hiredate` timestamp NOT NULL DEFAULT '2018-10-11 00:00:00'

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

mysql> create table test8(id int,hiredate timestamp default '0000-00-00 00:00:00');

ERROR 1067 (42000): Invalid default value for 'hiredate'

注意：

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'。

参考：https://www.cnblogs.com/mxwz/p/7520309.html

          https://www.cnblogs.com/musings/p/5913239.html

六、MySQL 获得毫秒、微秒及对毫秒、微秒的比较处理

1）、MySQL 较新的版本中（MySQL 6.0.5），也还没有产生微秒的函数，now() 只能精确到秒。 MySQL 中也没有存储带有毫秒、微秒的日期时间类型。

但，奇怪的是 MySQL 已经有抽取（extract）微秒的函数。例如：

select microsecond('12:00:00.123456'); -- 123456 select microsecond('1997-12-31 23:59:59.000010'); -- 10

select extract(microsecond from '12:00:00.123456'); -- 123456 select extract(microsecond from '1997-12-31 23:59:59.000010'); -- 10

select date_format('1997-12-31 23:59:59.000010', '%f'); -- 000010

尽管如此，想在 MySQL 获得毫秒、微秒还是要在应用层程序中想办法。假如在应用程序中获得包含微秒的时间：1997-12-31 23:59:59.000010，在 MySQL 存放时，可以设计两个字段：c1 datetime, c2 mediumint，分别存放日期和微秒。为什么不采用 char 来存储呢？用 char 类型需要 26 bytes，而 datetime + mediumint 只有 11（8+3） 字节。

2）、MYSQL的毫秒级时间比较

      A、一般来说，MYSQL使用时间比较一般是转换成时间格式来比较，如：

DATE_FORMAT('年-月-日 时:分:秒','%Y-%m-%d %h:%i:%s')

select DATE_FORMAT('2010-12-01 07:03:16','%Y-%m-%d %h:%i:%s')/*具体例子*/

如果要转换成毫秒级就要用 select DATE_FORMAT('2010-12-01 07:03:16.233','%Y-%m-%d %T:%f')-- 注意啦，T不能小写

 

一个查询的实例：

select * from test where DATE_FORMAT(timess,'%Y-%m-%d %T:%f') between DATE_FORMAT('2010-12-01 06:03:16.233','%Y-%m-%d %T:%f') and DATE_FORMAT('2010-12-01 08:03:16.733','%Y-%m-%d %T:%f')

在这里，要转换的字符串的毫秒前是用“.”表示小数位的。

    B、除此以外，还有一种可以直接拿字符串比较的：

select * from test where DATE_FORMAT(timess,'%Y-%m-%d %T:%f') between '2010-12-01 06:03:16:233' and '2010-12-01 08:03:16:733'

这样也可以。但是要注意这里的毫秒前的符号变成了":"，必须是这个符号，如果你写成了“.”查询结果是错误的。 

其实这2中写法都可以，不过，我还是建议最好用DATE_FORMA()函数转换的写法，因为在使用Navicat Lite这样的工具调试的时候，如果是直接比较字符串，有时候会报错。具体原因不明。

最后，还是要跟大家说一声：在用格式转换的时候，还是要分清大小写，因为很多人在这个上面不注意，结果导致调试失败。就如同上面的这个例子。不能用小写的t的格式，还是要用大写的T，否则结果会错误。

参考：https://blog.csdn.net/nonolive/article/details/44057839

3)、mysql解决datetime与timestamp精确到毫秒的问题

mysql> show create table tab2 \G;

*************************** 1. row ***************************

Table: tab2

Create Table: CREATE TABLE `tab2` (

`tab1_id` varchar(11) DEFAULT NULL,

`create` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> select CURRENT_TIMESTAMP(3);

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

| CURRENT_TIMESTAMP(3) |

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

| 2018-10-19 19:25:57.541 |

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

1 row in set (0.00 sec)

 

mysql> insert into tab2(tab1_id) values (11);

Query OK, 1 row affected (0.00 sec)

mysql> insert into tab2(tab1_id) values (22);

Query OK, 1 row affected (0.00 sec)

mysql> select * from tab2;

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

| tab1_id | create |

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

| 11 | 2018-10-19 19:17:42.358 |

| 22 | 2018-10-19 19:22:39.907 |

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

2 rows in set (0.00 sec)

 

mysql> insert into tab2(tab1_id,`create`) values (44,CURRENT_TIMESTAMP(3));

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from tab2;

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

| tab1_id | create |

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

| 11 | 2018-10-19 19:17:42.358 |

| 22 | 2018-10-19 19:22:39.907 |

| 44 | 2018-10-19 19:24:52.096 |

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

mysql> insert into tab2(tab1_id,`create`) values (55,'2018-10-19 19:25:57.541');

Query OK, 1 row affected (0.01 sec)

mysql> select * from tab2;

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

| tab1_id | create |

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

| 11 | 2018-10-19 19:17:42.358 |

| 22 | 2018-10-19 19:22:39.907 |

| 44 | 2018-10-19 19:24:52.096 |

| 55 | 2018-10-19 19:25:57.541 |

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

4 rows in set (0.00 sec)

 

mysql> show create table tab1 \G;

*************************** 1. row ***************************

Table: tab1

Create Table: CREATE TABLE `tab1` (

`tab1_id` varchar(11) DEFAULT NULL,

`create` timestamp(3) NULL DEFAULT NULL,

`create2` datetime(3) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> insert into tab1(tab1_id,`create`,create2) values (11,CURRENT_TIMESTAMP(3),CURRENT_TIMESTAMP(6));

Query OK, 1 row affected (0.00 sec)

mysql> insert into tab1(tab1_id,`create`,create2) values (22,'2018-10-19 19:25:57.541',CURRENT_TIMESTAMP(2));

Query OK, 1 row affected (0.00 sec)

mysql> insert into tab1(tab1_id,`create`,create2) values (33,'2018-10-19 19:25:57.541',CURRENT_TIMESTAMP(6));

Query OK, 1 row affected (0.01 sec)

mysql> insert into tab1(tab1_id,`create`,create2) values (44,'2018-10-19 19:25:57.541','2018-10-19 19:25:57.541');

Query OK, 1 row affected (0.00 sec)

mysql> insert into tab1(tab1_id,`create`,create2) values (44,'2018-10-19 19:25:57.541',now(3));

Query OK, 1 row affected (0.00 sec)

mysql> select * from tab1;

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

| tab1_id | create | create2 |

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

| 11 | 2018-10-19 19:44:45.713 | 2018-10-19 19:44:45.713 |

| 22 | 2018-10-19 19:25:57.541 | 2018-10-19 19:45:11.850 |

| 33 | 2018-10-19 19:25:57.541 | 2018-10-19 19:45:30.060 |

| 44 | 2018-10-19 19:25:57.541 | 2018-10-19 19:25:57.541 |

| 44 | 2018-10-19 19:25:57.541 | 2018-10-19 19:47:55.465 |

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

5 rows in set (0.00 sec)

TIMESTAMP(3)与 DATETIME(3)意思是保留3为毫秒数

TIMESTAMP(6)与 DATETIME(6)意思是保留6为毫秒数

修改已存在的表字段示例：ALTER TABLE tb_financial MODIFY CREATE_TIME DATETIME(3) DEFAULT NULL COMMENT '录入时间';

 插入日期可以用NOW(3)来控制精确的毫秒数，SELECT CURRENT_TIMESTAMP(3);也是可以的

参考:https://www.cnblogs.com/shihaiming/p/5853595.html

 

七、MySQL的10位或者13位时间戳转换格式化时间

1）、将时间转换为时间戳

select unix_timestamp('2009-10-26 10-06-07') ;
+---------------------------------------+
| unix_timestamp('2009-10-26 10-06-07') |
+---------------------------------------+
|                            1256522767 |
+---------------------------------------+

如果参数为空，则处理为当前时间

2）、将时间戳转换为时间

select from_unixtime(1256540102)

有些应用生成的时间戳是比这个多出三位，是毫秒表示，如果要转换，需要先将最后三位去掉（标准的10位数字，如果是13位的话可以以除以1000的方式），否则返回NULL

mysql> select from_unixtime(1539947679);

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

| from_unixtime(1539947679) |

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

| 2018-10-19 19:14:39 |

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

1 row in set (0.00 sec)

mysql> select from_unixtime(1539947679000);

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

| from_unixtime(1539947679000) |

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

| NULL |

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

1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME(1487655946901/1000);
+-----------------------------------+
| FROM_UNIXTIME(1487655946901/1000) |
+-----------------------------------+
| 2017-02-21 13:45:46.9010          |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2018-10-22 10:49:28 |
+---------------------+

mysql> select UNIX_TIMESTAMP(NOW()) * 1000;
+------------------------------+
| UNIX_TIMESTAMP(NOW()) * 1000 |
+------------------------------+
|                1540176603000 |
+------------------------------+
1 row in set (0.00 sec)

**需要注意的是select UNIX_TIMESTAMP(NOW())  输出的是秒

参考: https://blog.csdn.net/nonolive/article/details/44057839

       https://www.cnblogs.com/shihaiming/p/5853595.html