0

I have a timestamp table like this:

t_stamp
2023-04-28 20:20:28.497
2023-04-28 20:00:27.940
2023-04-27 19:37:27.413
2023-04-27 16:55:26.817
2023-04-26 15:16:26.273
2023-04-26 12:04:25.753

I tried so far:

SELECT distinct [t_stamp] as Date
,max([t_stamp]) - min([t_stamp]) as DeltaTime
,max([t_stamp]) as First_Tstamp
,min([t_stamp]) as Last_Tstamp
  FROM [testdb]
  group by t_stamp

Trying to get the output containing the date, the delta time between the first and the last timestamp, and then the respective ones. Like this:

Date DeltaTime First_Tstamp Last_Tstamp
2023-04-28 00:20:28 2023-04-28 20:00:27 2023-04-28 20:20:28
2023-04-27 02:17:59 2023-04-27 16:55:26.817 2023-04-27 19:37:27
2023-04-26 03:11:01 2023-04-26 12:04:25.753 2023-04-26 15:16:26

but subtracting or using max() and min() always ends up in same value and in consequence, my Delta is always 0:

Date DeltaTime First_Tstamp Last_Tstamp
2023-04-28 00:00:00 2023-04-28 20:20:28 2023-04-28 20:20:28
2023-04-27 00:00:00 2023-04-27 19:37:27 2023-04-27 19:37:27
2023-04-26 00:00:00 2023-04-26 15:16:26 2023-04-26 15:16:26
Thom A
  • 88,727
  • 11
  • 45
  • 75
Luciano Furini
  • 1
  • 2
  • YOu need to `CAST`/`CONVERT` your `datetime` (it's not a `timestamp`, as `timestamp` in SQL Server is a deprecated synonym for `rowversion`, which is a `binary(8)` value) in your `SELECT` and `GROUP BY`. Also putting a `DISTINCT` in your query makes little sense; your `GROUP BY` is already putting your results into *distinct* groups. The `DISTINCT` is only likely to be detrimental to the query's performance. – Thom A May 11 '23 at 15:03

0 Answers0