timestamp manupulation

Vineet S 145

Hey, here is the table & it contains timestamp at the end. check the output

create table customer (CustomerName char(22), ContactName char(22), Address char(22), City char(22)
, PostalCode char(22), Country char(22),time DATETIME DEFAULT GETDATE())
INSERT INTO customer (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
INSERT INTO customer (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal1', 'Tom B. Erichsen1', 'Skagen 211', 'Stavanger1', '4001', 'Norway1');
INSERT INTO customer (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal2', 'Tom B. Erichsen2', 'Skagen 212', 'Stavanger2', '4002', 'Norway2');
INSERT INTO customer (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal2', 'Tom B. Erichsen2', 'Skagen 212', 'Stavanger2', '4002', 'Norway2');

for row 2nd row , it should take previous timestamp +1 second for 3rd row, it should take next timestamp -1 second and it should keep happen when more records added User's image

desired: User's image

Vineet S 145 Reputation points

2024-02-26T12:52:57.11+00:00

Hey, any difficulties in understanding
Bruce (SqlWork.com) 56,286 Reputation points

2024-02-26T16:01:05.4633333+00:00

First what determines the insert order, that is what column do you use for the order by to get the row order. Second what logic determines if you take the previous or next row, or not at all?
Vineet S 145 Reputation points

2024-02-26T23:52:05.7966667+00:00

SQL statement pls
CosmogHong-MSFT 23,166 Reputation points Microsoft Vendor

2024-02-27T03:13:42.6466667+00:00
Hi @Vineet S

Hey, any difficulties in understanding

In your desired result, why the second-row timestamp is 09:57:24?

What do you mean and it should keep happen when more records added? Are there not only 4 rows? Or you mean update timestamp dynamically.
Vineet S 145 Reputation points

2024-02-27T05:17:08.44+00:00

Generally 2 nd row... We do not get timestamp sometimes, so it should be previous row timestamp +1 same for last row.. No need to repeat as of now
Erland Sommarskog 101K Reputation points MVP

2024-02-27T22:23:23.5933333+00:00

The fact that here are timestamps missing was not clear from your original post.

In any case, if there is a missing timestamp, how would you know which is the "previous" row? When you insert the row, you can get the max timestamp and add a second. But if data was inserted with NULL in timestamp, you need another way to determine the previous row.
Vineet S 145 Reputation points

2024-02-27T22:33:19.1733333+00:00

You can take with timestamp also to calculate with previous row.. But help calculate
Erland Sommarskog 101K Reputation points MVP

2024-02-27T22:39:37.6233333+00:00

The calculation is the easy part: DATEADD(SECOND, 1, timestamp). But you need to know how to identify the previous row.
Vineet S 145 Reputation points

2024-02-27T23:13:34.1833333+00:00

That condition only requires
Vineet S 145 Reputation points

2024-02-28T17:21:23.0333333+00:00

qny query seggestion
Erland Sommarskog 101K Reputation points MVP

2024-02-28T21:58:39.5+00:00

That condition only requires

qny query seggestion

Not sure what you are trying to say here.

In any case, as long as you don't explain how you identify the "previous row", there is no way we can write a full query.
Vineet S 145 Reputation points

2024-02-28T22:40:05.05+00:00

You can consider 3 things top row 2nf row, last row

1 answer

Erland Sommarskog 101K Reputation points MVP

2024-02-28T22:47:05.8133333+00:00

You can consider 3 things top row 2nf row, last row

I'm sorry that does not tell me anything. What defines a "top row"?

Keep in mind that tables are unordered objects. You may see an order when run a query, but that order is just accidental, unless there is something in the data that defines the order.

And if there is nothing that defines the order, the answer is simple: it can't be done.
Please sign in to rate this answer.
Vineet S 145 Reputation points

2024-02-28T23:33:55.7366667+00:00

Top row remain same Only changes in 2nnd and last row
Sign in to comment