Datetime and timestamp -- the timestamp is the absolute value and the date is the relative value.

Problem: the system time is different from the database time. the system time is, and the time saved to the database is.

 

The first intuition is caused by different time zones.

 

First look at a piece of code:

 

 

Java code

1. Public static void main (string [] ARGs ){
2. // System. Out. println (timezone. getdefault ());
4. Simpledateformat SDF = new simpledateformat ("yyyy-mm-dd hh: mm: SS ");
5. Calendar c = calendar. getinstance ();
6. // System. Out. println (C. gettime ());
7. System. Out. println (SDF. Format (C. gettime ()));
8. System. Out. println (C. gettimeinmillis ());
10. Timezone. setdefault (timezone. gettimezone ("GMT + 5:00 "));
11. SDF. settimezone (timezone. getdefault ());
13. // System. Out. println (C. gettime ());
14. // System. Out. println (C. gettime (). gettimezoneoffset ());
15. System. Out. println (SDF. Format (C. gettime ()));
16. System. Out. println (C. gettimeinmillis ());
17. }

 

 

 

 

Output result:

2011-11-25 10:33:21

1322188401796

2011-11-25 07:33:21

1322188401796

 

This indicates that the display of time is determined by the time zone. The time represents an absolute number of milliseconds from the standard time and will not change at any time. It is important to understand this.

 

So the time in the database should also have a time zone concept. What is the solution? We always think that the actual storage time in the database is in milliseconds. The time we see on the client must have been formatted by the database.

However, the results are not completely the same. Next we will discuss the two time types datetime and timestamp in the database.

 

Online Domain Name

Datetime-stores the date and time, accurate to seconds, without time zone information

Timestamp-timestamp: stores the date, time, and time zone information. The second value is accurate to the last six decimal places.

Note that the timestamp here is not the time at the root of sqlserver. It only records the relative time sequence and does not record the specific time. I think it should be called the data version number.

 

First, test in sqlserver:

 

Select getdate ()

 

The execution result is the same as imagined. He can change the time zone of the operating system. He must have obtained the system time zone information and formatted the current number of milliseconds.

 

Restore the system time zone to GMT +, create a test table, and insert two pieces of data

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;

 

The database query result is

1 10:47:23. 750 10:47:23. 750

2 10:47:27. 513 10:47:27. 513

 

Now, change the system time zone to GMT +, insert two more data entries, and modify the first data entry.

 

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;

 

The database query result is

1 07:50:20. 373 10:47:23. 750

2 10:47:27. 513 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

 

Since datetime does not have time zone information and only has year, month, day, hour, minute, and second information, what is saved is a few points, and the difference between the two operations is 3 hours.

 

Select T. *, T. time2-t.time1 from test_timezone t where T. tid = 1

 

 

Let's look at the timestamp type again. sqlserver a table can only have one timestamp column, and the timestamp column does not need to be operated. It is automatically updated when data rows are inserted or updated.

Create test table

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;

 

Query results:

1 0x0000000000000000200a

2 0x000000000000200b

3 0x000000000000200c

 

Update test_timezone2 set tid = 4 where tid = 3;

 

Select * From test_timezone2;

 

Query results:

1 0x0000000000000000200a

2 0x000000000000200b

4 0x000000000000200e

 

This timestamp is mainly used to handle concurrency issues. It can be used as a credential for checking whether the data has been modified, which can improve the concurrency performance. Again, it is clear that the sqlserver timestamp is not the specific time.

 

The current time point is unknown when the time zone is restored to GMT +.

 

 

 

Next, perform a test in MySQL:

 

Select now ()

 

Modifying the system time zone does not affect the query results, which is different from SQL Server. After changing the time zone, restart MySQL and then execute the command. MySQL records the system time zone at startup, instead of reading the system time zone in real time.

 

Restore the time zone to GMT + and create a new table

Create Table test_timezone (

TID int,

Time1 datetime,

Time2 timestamp,

Time3 Timestamp

);

Insert data

Insert into test_timezone (TID) values (1 );

Insert into test_timezone (TID) values (2 );

Insert into test_timezone values (3, now ());

Insert into test_timezone values (4, now ());

 

Select * From test_timezone

Query Result

 

MySQL allows multiple timestamp columns, but only the first column is automatically updated. The default value is

Current_timestamp.

 

Restore the time zone to GMT +, restart MySQL, and run the query

The results show that the datetime time does not change with the system time zone, and the timestamp changes with the system time zone, and sqlserver is completely different. MySQL records the number of milliseconds in the timestamp field and is displayed after formatting according to the initial system time zone.

 

In addition, Oracle has no testing environment.

 

Conclusion:

The datatime type only saves the information of year, month, day, hour, minute, and second, excluding the time zone.

Timestamp timestamp. Different databases have different implementations. Do not use the timestamp as a business column, nor use it as an index or key. It will be automatically updated.

Datetime and timestamp -- the timestamp is the absolute value and the date is the relative value.