I have a table having three fields Id (Integer) - Unique, Open Date (Datetime), Close Date(DateTime):
Id Open Date Close Date
1 2019-07-03 16:28:39.497 2019-07-04 16:28:39.497
2 2019-07-04 15:28:39.497 2019-07-05 19:28:39.497
…..N
I want to calculate the all the timestamps between open date and close date with an interval of each minute.
So the final output I want is like this:
Id Open Date Close Date TimeStamp Range
1 2019-07-03 16:28:39.497 2019-07-04 16:28:39.497 2019-07-03 16:29:00.0000
1 2019-07-03 16:28:39.497 2019-07-04 16:28:39.497 2019-07-03 16:30:00.0000
1 2019-07-03 16:28:39.497 2019-07-04 16:28:39.497 2019-07-03 16:31:00.0000
1 2019-07-03 16:28:39.497 2019-07-04 16:28:39.497 …..........................
1 2019-07-03 16:28:39.497 2019-07-04 16:28:39.497 2019-07-04 16:27:00.0000
2 2019-07-04 15:28:39.497 2019-07-05 19:28:39.497 2019-07-04 15:29:00.0000
2 2019-07-04 15:28:39.497 2019-07-05 19:28:39.497 2019-07-04 15:30:00.0000
2 2019-07-04 15:28:39.497 2019-07-05 19:28:39.497 2019-07-04 15:31:00.0000
2 2019-07-04 15:28:39.497 2019-07-05 19:28:39.497 ….................................
2 2019-07-04 15:28:39.497 2019-07-05 19:28:39.497 2019-07-05 19:27:00.0000
N …............................ …........................... …......................................
Would someone write the SQL query for this?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…