Menu
I've been staring at this for quite a while and can't figure out how to go about doing it. I don't really have any code to post because I haven't even gotten close to figuring it out yet and everything I come up with just doesn't make any sense.
Anyways, we have an application that collects data every 5 minutes based on the max(readtime) in the table (but also will only show real values if the time is a multiple of 5 and is at :00.000 seconds.) For the most part, this is fine, but sometimes the data will get out of sync and cause a bunch of 0 values in our table.
Currently, we've just been going in, grabbing the latest readtime, and hardcoding the new date that we'd like to use. I'm looking for a script I can use to automate this. For the purposes of this question, we can use GETDATE().
DECLARE @readtime DATETIME = GETDATE() --2011-08-09 08:51:19.237
-- I want it to look like 2011-08-09 08:50:00.000
276
We can use DATEADD() function like below to add hours to DateTime in Sql Server. DATEADD() functions first parameter value can be hour or hh all will return the same result.
To update with the current date and time: UPDATE table_name SET date_field = CURRENT_TIMESTAMP; To update with a specific date value: UPDATE table_name SET date_field = 'YYYY-MM-DD HH:MM:SS.
To add minutes to a datetime you can use DATE_ADD() function from MySQL. In PHP, you can use strtotime(). select date_add(yourColumnName,interval 30 minute) from yourTableName; To use the above syntax, let us create a table.
We can use DATEADD() function like below to add seconds to DateTime in Sql Server. DATEADD() functions first parameter value can be second or ss or s all will return the same result.
Try this.
It basically checks minutes to be divisible by 5, seconds by 6 and ms by 1000, and if they aren't it subtracts the remainder from each:
DECLARE @dt datetime = '2011-08-09 08:51:19.237'
SELECT DATEADD(
MILLISECOND,
-(DATEPART(MILLISECOND, @dt) % 1000),
DATEADD(
SECOND,
-(DATEPART(second, @dt) % 60),
DATEADD(
minute,
-(DATEPART(minute, @dt) % 5), @dt )
)
)
Output:
2011-08-09 08:50:00.000
83
Round down to nearest 5 minute.
DECLARE @readtime DATETIME =
DATEADD(minute, 5*FLOOR(DATEDIFF(minute, 0, GETDATE())/5.0), 0)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
