【IT168技术文档】1. 环境版本:
OS : LINUX AS4
MYSQL: 5.0.51a-log
ENGINE : Myisam
2. 属性
TIMESTAMP , Stored as unix time(), low byte first.
共占4个字节(32位),范围'1970-01-01 00:00:00'到'2037-12-31 23:59:59' ,格式'YYY-MM-DD HH:MM:SS'
3. 一种算法:
3.1分析
在存储时,
1) 通过UNIX_TIMESTAMP(date)函数,将用户输入的时间转换成unix time();
select UNIX_TIMESTAMP(date);
2) 将第1步的结果转换成十六进制 并反向存储.
在从数据库里取数据时:
1) 从磁盘定位到行后,按反向顺序取出,并转换成十进制;
2) 将第1步的结果,用FROM_UNIXTIME()函数获得时间.
select FROM_UNIXTIME(unix_timestamp);
注意,以上两个函数都与系统参数:time_zone有关.
如果两个操作时TIME_ZONE的值不一样,那么得到的日期前后将不一致.
比如,当前TIME_ZONE='+08:00' ,time = '2009-03-09 14:22:48' ,
1) select UNIX_TIMESTAMP('2009-03-09 14:22:48');
--> 1236579768
2) select conv(1236579768,10,16) ;
--> 49B4B5B8
3) 反向存储 : B8 B5 B4 49
3.2 验证
下面我们可以来验证一下上面的分析结果:
set @@time_zone = '+8:00';
drop table if exists heyf;
create table heyf (id int ,curtime timestamp) type myisam;
insert into heyf values (11,'2009-03-09 14:22:48');
system hexdump /opt/mysql/data/test/heyf.MYD
------------------------------------
0000000 0bfd 0000 b800 b4b5 0049
0000009
------------------------------------
其中:
fd : 标志位
0b 00 00 00 : COL1 ,INT = 11
b8 b5 b4 49 : 即时间.由于是反向存储,所以需要反向读取: 49b4b5b8
在这里我们正好来进行反演测试:
1) b8 b5 b4 49 --> 49b4b5b8
2) select conv('49b4b5b8',16,10) ;
--> 1236579768
3) select FROM_UNIXTIME(1236579768) ;
--> '2009-03-09 14:22:48'
我们看到,这个时间前后刚好一致!
4. 另外一种算法:
4.1 分析
存储时:
1) 将用户输入的时间,与当前SESSION TIME_ZONE 相减. (得到标准格林威治时间)
2) 将第1步结果与时间" 1970-01-01 00:00:00"相减,得到X秒
3) 将第2步结果转换与十六进制,并反向
读取时:
1) 定位到行后,将字段值反向并转换成十进制;
2) 将第1步结果与 " 1970-01-01 00:00:00"相加;
3) 将第2步结果与 当前SESSION TIME_ZONE 相加,并将结果输出给用户
4.2 验证:
比如,当前TIME_ZONE='+08:00' ,time = '2008-05-09 15:09:48' ,
存储时:
1) select '2008-05-09 15:09:48'- interval 8 hour ;
--> 2008-05-09 07:09:48
2) select datediff('2008-05-09','1970-01-01')*24*3600 + time_to_sec('07:09:48');
--> 1210316988
3) select conv(1210316988,10,16) ;
--> 4823F8BC
反向:
--> BC F8 32 48
下面来看一下存储的值:
set @@time_zone = '+8:00';
drop table if exists heyf;
create table heyf (id int ,curtime timestamp) type myisam;
insert into heyf values (11,'2008-05-09 15:09:48');
system hexdump /opt/mysql/data/test/heyf.MYD
------------------------------------
0000000 0bfd 0000 bc00 23f8 0048
0000009
------------------------------------
其中:
-------------------------------
fd : 标志位
0b 00 00 00 : COL1 ,INT = 11
bc f8 23 48 : 即时间.由于是反向存储,所以需要反向读取: 48 23 f8 bc
-------------------------------
反向读取过程:
1) bc f8 23 48 --> 4823f8bc
2) select '1970-01-01 00:00:00' + interval conv("4823f8bc",16,10) second ;
-->2008-05-09 07:09:48
3) select select @@time_zone;
--> +08:00
select '2008-05-09 07:09:48' + interval 8 hour ;
--> 2008-05-09 15:09:48
结果完全一致.
5. 当TIME_ZONE 不一样时
drop table if exists heyf;
create table heyf (id int ,curtime timestamp) type myisam;
set @@time_zone = '+8:00';
insert into heyf values (11,'2008-08-09 15:53:48');
(root@FuncTestDB:)> select * from heyf ;
+------+---------------------+
| id | curtime |
+------+---------------------+
| 11 | 2008-08-09 15:53:48 |
+------+---------------------+
1 row in set (0.00 sec)
(root@FuncTestDB:)> set @@time_zone = '+6:00';
Query OK, 0 rows affected (0.00 sec)
(root@FuncTestDB:)> select * from heyf ;
+------+---------------------+
| id | curtime |
+------+---------------------+
| 11 | 2008-08-09 13:53:48 |
+------+---------------------+
1 row in set (0.00 sec)
(root@FuncTestDB:)> set @@time_zone = '+4:00';
Query OK, 0 rows affected (0.00 sec)
(root@FuncTestDB:)> select * from heyf ;
+------+---------------------+
| id | curtime |
+------+---------------------+
| 11 | 2008-08-09 11:53:48 |
+------+---------------------+
1 row in set (0.00 sec)
我们看到,时间在随着TIME_ZONE的变化而变化;
timestamp 实际存储的是一个与'19700101000000'的时间差值(秒为单位),在提取时会根据当前的TIME_ZONE来重新计算.
所以我们在使用这种类型的数据时,要特别注意系统变量:TIME_ZONE。
