January 6, 2017 at 3:51 am
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! π π π
January 6, 2017 at 4:03 am
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 recoverability
January 6, 2017 at 4:13 am
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
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
January 6, 2017 at 4:18 am
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...)
January 6, 2017 at 4:21 am
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.
January 6, 2017 at 4:26 am
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 recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply