How to correct this timestam for the current day?
-
Hello, I'm creating a timestamp of the actual day (from this day's midnight to the other's), but I'm having some issues...
One of the problems is that with the code that I have, I can only get the results from the past day to this day.
Code:SELECT "num",
dateadd(hour, -(num), DATEDIFF(d, 0, GETDATE())) AS interval_start,
dateadd(hour, -(num + 1), DATEDIFF(d, 0, GETDATE())) AS interval_end
FROM numbers
GO
Here it is a PrintScreen, so you guys can understand what I'm saying:
---------------------------------------------------------------------------------------------
Another problem that I have is, like you might seen, the timestamp is upside down, starting on the left corner...
Can I fix this problem easily with the code I have?
I aprecciate any kind of help, since I'm new to the world of SQL and I'm still learning, hehe. 🙂
Many thanks! 😀 😀 😀
-
I'm not sure I understand what results you're trying to get.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (1/6/2017)
I'm not sure I understand what results you're trying to get.Neither am I...
But, assuming that your numbers table is a list of consecutive integers, then removing the '-' will create a list of hour intervals in ascending order.
SELECT "num",dateadd(hour, (num), DATEDIFF(d, 0, GETDATE())) AS interval_start,
dateadd(hour, (num + 1), DATEDIFF(d, 0, GETDATE())) AS interval_end
FROM numbers
GO
On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a PhilosopherHow to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi, sorry for not beeing so explicit...
I'm trying to create a daily Report in a SCADA software called Ignition, basically what I wanted to do with the daily report is to have the average value of a sensor for every hour of the day, and this is why I need to use SQL.
In SQL, I'm creating a time range from the actual day's midnight from the next day's midnight, so I can have it implemented in the SCADA software via database connection.
My objective is to have a timestamp like this one:
2017/1/06 - 00:00 -> 2017/1/06 - 01:00
2017/1/06 - 01:00 -> 2017/1/06 - 02:00
2017/1/06 - 02:00 -> 2017/1/06 - 03:00
2017/1/06 - 03:00 -> 2017/1/06 - 04:00
(and so on...)
-
I already tried to explain with more detail to GilaMonster
But yes, that's it! It worked like a charm xD
Thank you so much for your help anyway hehehe.
-
daniel.sousa (1/6/2017)
My objective is to have a timestamp like this one:2017/1/06 - 00:00 -> 2017/1/06 - 01:00
2017/1/06 - 01:00 -> 2017/1/06 - 02:00
2017/1/06 - 02:00 -> 2017/1/06 - 03:00
2017/1/06 - 03:00 -> 2017/1/06 - 04:00
(and so on...)
Remove the negatives and offset the whole thing by -1 to start at midnight
SELECT Num,dateadd(hour, Num - 1, DATEDIFF(d, 0, GETDATE())) AS interval_start,
dateadd(hour, Num, DATEDIFF(d, 0, GETDATE())) AS interval_end
FROM numbers
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply