Timestamp and date-time

Question

I have a table with two columns. The first column is the timestamp (currentdate), and the other is the datetime (dateReturn) column. To get the difference between currentdate and dateReturn from the number of days, do you need to get this value?

Answer 1

No, It is Immpossible.

Contrary to popular belief (and the name itself), the timestamp column is not actually time-related.

Disclaimer This answer is specific to Sql Server (according to the question tags), other database engines may have different implementations here. *

Instead, it is a 64-bit number that is constantly increasing. The value of the number is shared between all tables in the same database with this timestamp column. In other words, if you have two such tables, change the row in one and change the row in the other, the timestamp value in the first table will be equal to X, and in the second table it will be X + 1.

according to the timestamp documentation :

Each database has a counter that increments for each insert or update operation that is performed on a table containing a timestamp column in the database. This counter is the database timestamp. This keeps track of the relative time in the database, not the actual time that the clock may be associated with.

Thus, the value is not related to the actual date and / or time at which the last row was inserted or changed, and cannot be converted to such a date / time.

The only purpose of the timestamp column is to have something unique that is guaranteed to increase. Since the value is 64 bits, you will have access to operations 18 446 744 073 709 551 616 , which require a timestamp column before resetting the value. If you have a database that can handle a million of such operations every second, you still have to wait about 584,554 years until the value is reset, so I would say that it meets the requirements of uniqueness.

Answer 2

The timestamp data type in the SQL server does not store the date or time. It is rather a column supporting the version, which is updated every time the row is updated. Therefore, you should revise the data type of this column.

Answer 3

If your column has a (relatively rare) type of timestamp , Lasse W. Carlsen's answer is correct.

If both columns are of type datetime , you can:

 select datediff(day, dateReturn, currentdate) from YourTable 

See dated function .