`
bd_cool
  • 浏览: 60073 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

datetime和timestamp在不同数据库中的应用

阅读更多

为什么总是以为没有什么好学的,只有碰到问题了,才知道你有很多东西都不清楚,或者根本就不知道。

 

遇到的问题:系统时间与数据库时间不一致,系统时间是8:20,存到数据库里是0:20

 

第一直觉是时区不同导致的。

 

先看一段代码:

 

	public static void main(String[] args) {
		//System.out.println(TimeZone.getDefault());
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Calendar c = Calendar.getInstance();
		//System.out.println(c.getTime());
		System.out.println(sdf.format(c.getTime()));
		System.out.println(c.getTimeInMillis());
		
		TimeZone.setDefault(TimeZone.getTimeZone("GMT+5:00"));
		sdf.setTimeZone(TimeZone.getDefault());
		
		//System.out.println(c.getTime());
		//System.out.println(c.getTime().getTimezoneOffset());
		System.out.println(sdf.format(c.getTime()));
		System.out.println(c.getTimeInMillis());
	}

 

 

输出结果:

2011-11-25 10:33:21

1322188401796

2011-11-25 07:33:21

1322188401796

 

这说明,时间的显示是由时区决定的,时间所表示的距标准时间毫秒数是绝对的,不会随时区不同而改变。理解这一点很重要。

 

那么数据库里的时间也应该有个时区概念,那到底是如何处理的呢?一直这么认为的,时间在数据库里实际存的是毫秒数。那我们在客户端看到的时间一定是经过数据库格式化以后的。

但结果不完全是这样的,下面我们要讨论数据库中的两个时间类型 datetime timestamp

 

网上有文称

datetime - 存储日期和时间部分,精确到秒,没有时区信息

timestamp - 时间戳,存储日期、时间和时区信息,秒值精确到小数点后6

注意这里的时间戳,在sqlserver中根本就不是时间,他只记录相对时间的先后,不记录具体时间。我认为应该叫做数据版本号。

 

首先在sqlserver中测试:

 

select getdate()

 

执行结果跟想像的一样,操作系统的时区如何修改,他都能随之变化,他肯定获得了系统的时区信息,然后对当前毫秒数格式化。

 

把系统时区恢复到GMT+8:00,创建一个测试表,并插入两条数据

create table TEST_TIMEZONE(

  tid int,

  time1 datetime ,

  time2 datetime

);

 

insert into TEST_TIMEZONE values(1,getdate(),getdate());

insert into TEST_TIMEZONE values(2,getdate(),getdate());

 

select * from TEST_TIMEZONE;

 

这时数据库查询结果为

1     2011-11-25 10:47:23.750       2011-11-25 10:47:23.750

2     2011-11-25 10:47:27.513       2011-11-25 10:47:27.513

 

现在修改系统时区为GMT+5:00,再插入两条数据,并修改第一条数据

 

insert into TEST_TIMEZONE values(3,getdate(),getdate());

insert into TEST_TIMEZONE values(4,getdate(),getdate());

 

update TEST_TIMEZONE set time1=getdate() where tid=1;

 

select * from TEST_TIMEZONE;

 

这时数据库查询结果为

1     2011-11-25 07:50:20.373       2011-11-25 10:47:23.750

2     2011-11-25 10:47:27.513       2011-11-25 10:47:27.513

3     2011-11-25 07:50:15.920       2011-11-25 07:50:15.920

4     2011-11-25 07:50:18.500       2011-11-25 07:50:18.500

 

因为datetime没有时区信息,只有年月日时分秒,所以保存的是几点就是几点,两次操作差了3个小时。

 

select t.*,t.time2-t.time1 from TEST_TIMEZONE t where t.tid=1

 

 

我们再看时间戳类型,sqlserver一个表只能有一个时间戳列,而且时间戳列不用操作,在数据行插入或更新时自动更新。

新建测试表

create table TEST_TIMEZONE2(

  tid int,

  time1 timestamp

);

 

insert into TEST_TIMEZONE2(tid) values(1);

insert into TEST_TIMEZONE2(tid) values(2);

insert into TEST_TIMEZONE2(tid) values(3);

 

 

select * from TEST_TIMEZONE2;

 

查询结果:

1     0x000000000000200A

2     0x000000000000200B

3     0x000000000000200C

 

update TEST_TIMEZONE2 set tid=4 where tid=3;

 

select * from TEST_TIMEZONE2;

 

查询结果:

1     0x000000000000200A

2     0x000000000000200B

4     0x000000000000200E

 

这个时间戳主要用在处理并发问题上,做为数据是否已被修改的凭证,可以提高并发性能。 再次明确sqlserver的时间戳不是具体时间。

 

恢复一下时区到GMT+8:00,都不知道现在几点了。

 

 

接下来,在mysql做个测试:

 

select now()

 

修改系统时区对查询结果没有影响,这与sqlserver不同。修改时区后,重启mysql,再执行有效果了。说明mysql在启动时记录了系统时区,而不是实时的读取系统时区。

 

恢复时区到GMT+8:00,新建

create table TEST_TIMEZONE(

  tid int,

  time1 datetime ,

  time2 timestamp ,

  time3 timestamp

);

插入数据

insert into TEST_TIMEZONE(tid) values(1);

insert into TEST_TIMEZONE(tid) values(2);

insert into TEST_TIMEZONE values(3,now(),now(),now());

insert into TEST_TIMEZONE values(4,now(),now(),now());

 

select * from TEST_TIMEZONE

查询结果

 



 

 

Mysql允许多个timestamp列,但只有第一列会自动更新,默认值

CURRENT_TIMESTAMP

 

恢复时区到GMT+5:00,重启动mysql,执行查询



  

结果说明datetime的时间不随系统时区而变化,timestamp会随系统时区变化而变化,也sqlserver完全不同。Mysqltimestamp字段记录的是毫秒数,并且按初始的系统时区格式化后显示。

 

另外对oracle现在没有测试环境。

 

结论:

Datatime类型只保存年月日时分秒信息,不含时区。

Timestamp时间戳,不同数据库有不同的实现,不要用做业务列,更不能作为索引或键使用,他会自动被更新。

 

记得把时区恢复回去,不然比别人晚了好几个小时^_^

  • 大小: 20.9 KB
  • 大小: 29.2 KB
3
3
分享到:
评论
8 楼 bd_cool 2011-12-06  
ABC
DEF
7 楼 bd_cool 2011-11-26  
不是跨国的系统一般不用考虑。

但我们这个系统需要多点部署,有四十多个个,只有其中一个点上的服务器时间搞错了,引起了这方面的问题。所以才把相关的内容整理一下,加深了点理解。
6 楼 jyjava 2011-11-26  
细节问题,没考虑过
5 楼 bd_cool 2011-11-26  
4 楼 cheeruplc 2011-11-25  
3 楼 bd_cool 2011-11-25  
无论是Calendar.getInstance()还是new Date(),都会取系统默认时区,是在jvm启动时读系统的时区。

如果系统时区错误,就会导致数据时间混乱,可以在jvm启动时加时区参数
-Duser.timezone=GMT+8:00
这样就能正确处理北京时间了。(如果不用北京时间,改成你想要的就可以了)
2 楼 bd_cool 2011-11-25  
看到php写的一个论坛,mysql数据库,所有时间都是int(10)
1 楼 bd_cool 2011-11-25  
数据库的保存形式,大概有了较清晰的认识。
那开发过程中到底应该如何处理时区呢?

相关推荐

    python timestamp和datetime之间转换详解

    在实际应用中,我们常常需要将从数据库或其他来源获取的字符串形式的日期时间转换为时间戳。以下是一个示例函数,用于将字符串日期时间转换为时间戳: ```python import time from datetime import datetime def ...

    spirng-boot 的时间类型(date、datetime、timestamp)的全局配置

    在Spring Boot应用中,日期和时间的处理是开发过程中常见的任务。Spring Boot提供了一种方便的方式来全局配置日期和时间类型,包括date、datetime和timestamp,这使得与数据库交互时能够更加便捷地处理时间数据。本...

    MySQL中datetime和timestamp的区别及使用详解

    在MySQL数据库中,datetime和timestamp是两种常用的日期和时间数据类型,它们虽然都可以用来存储日期和时间信息,但在使用和处理上存在一些显著的区别。本文将深入探讨这两种数据类型的差异以及如何在实际应用中选择...

    向数据库插入datetime类型的数据

    - 不同的数据库管理系统可能有不同的日期时间类型和转换函数,因此在实际应用中需要根据具体的数据库类型来选择合适的方法。 - 当使用字符串直接在SQL语句中进行转换时,需要注意字符串的格式必须与数据库所期望的...

    Mysql中的Datetime和Timestamp比较

    MySQL数据库系统中,时间数据类型是用来存储日期和时间值的,其中包括`DATE`、`DATETIME`和`TIMESTAMP`。这些类型在某些方面相似,但在其他方面却有着明显的区别。下面将详细介绍`DATETIME`和`TIMESTAMP`的比较,并...

    数据库应用技术:数据类型.pptx

    在MySQL中,日期时间类型包括date、time、datetime、timestamp和year等。每一种日期时间类型都有合法的取值范围,当插入不合法的值时,系统会将“零”值插入字段中。 在数据库系统中,正确地选择数据类型对于数据库...

    SQL Server和国产数据库之间数据移植研究.pdf

    在表1中,列举了Kingbase ES 3.1和SQL Server 2000之间共同的数据类型,比如datetime、timestamp、boolean等,以及各自特有的数据类型。数据类型的对应关系是数据迁移过程中的一个重要参考,能够帮助开发者了解如何...

    MySQL数据库应用、设计与管理任务驱动教程(微课版)-课后练习参考答案.pdf

    - 日期时间类型包括 `Year`、`Time`、`DateTime` 和 `Timestamp` 等。 - **创建和删除数据表**: - 创建数据表:`Create Table test` - 删除数据表:`Drop Table test` - **查看数据表结构**:可以通过 `Describe...

    数据库基础与应用辅导八---综合复习题及答案

    6. Timestamp在SQL Server中不是日期时间类型,而是一种版本标识符,用于记录数据行的修改历史,与日期时间无关,且不同于传统的数据库加锁技术。 7. 在SQL语句执行中,SELECT * FROM [select]会因为语法错误而无法...

    MySQL数据库习题和实践答案.rar

    MySQL数据库是世界上最受欢迎的开源关系型数据库之一,广泛应用于各种规模的企业、网站和应用程序中。这份"MySQL数据库习题和实践答案"的压缩包文件,很可能是为了帮助学习者加深对MySQL理论知识的理解并提高实际...

    C#更新SQLServer中TimeStamp字段(时间戳)的方法

    本文将深入探讨如何在C#中读取和更新SQL Server中的Timestamp字段。 首先,时间戳(TimeStamp)字段在SQL Server中实际上是变长的二进制数据,它不表示特定的时间点,而是表示自表创建以来数据行的更改次数。由于它...

    mysql时间类型对应的java类型1

    在IT领域,数据库管理和编程语言之间的数据类型对应关系是至关重要的。本文将深入探讨MySQL数据库中的时间类型以及它们在...在实际应用中,还要注意处理时区差异、日期时间格式化以及数据库与Java之间的兼容性问题。

    SQL Server数据库应用辅导八---综合复习题.pdf

    【SQL Server数据库应用辅导综合复习知识点】 1. SQL Server特性:SQL Server支持XML、用户自定义函数和邮件集成,但不支持网状数据模型。网状数据模型是一种早期的数据库模型,而SQL Server采用的关系模型。 2. ...

    MySQL数据库基础应用讲解

    MySQL数据库是全球最受欢迎的开源关系型数据库管理系统之一,尤其在LAMP(Linux、Apache、MySQL...在实际应用中,还需要结合具体需求,进一步学习高级特性,如视图、存储过程、触发器等,以提升数据库的灵活性和效率。

    数据库重要知识点梳理

    日期时间类型字段在数据库中有多种类型,如DATETIME、TIMESTAMP、DATE、YEAR和TIME。每种类型占用不同的存储空间,并有特定的取值范围。例如,DATETIME类型占用8个字节,可以存储从1000年到9999年的日期时间数据。 ...

    c#Datetime格式和时间戳转换.zip

    此外,时间戳在数据库存储中也常见,因为它占用更少的空间,并且在比较和计算时更加高效。 在实际应用中,你可能会遇到不同的时间戳格式,例如毫秒级时间戳,此时可以调整上述代码以适应不同的需求。同时,需要注意...

    计算机软件-商业源码-实例84 处理数据库中日期型字段的显示与输入.zip

    在关系型数据库管理系统(RDBMS)如MySQL、Oracle、SQL Server中,日期和时间数据类型包括DATE、TIME、DATETIME、TIMESTAMP等。DATE类型存储年月日,TIME类型存储时分秒,DATETIME和TIMESTAMP则同时包含日期和时间,...

    DateTime with ODBC日期时间和ODBC

    在IT行业中,数据库应用是核心领域之一,而ODBC(Open Database Connectivity)是连接各种数据库系统的重要标准。...在实际项目中,结合ODBC的API和自定义DateTime类,可以创建出健壮且易于维护的数据库应用程序。

Global site tag (gtag.js) - Google Analytics