Rounding datetimes

[SeaPorpoise]

Dear Forum,



I have a table called GPS with a datetime field called 'UTC', e.g. 29/07/2015 12:02:42. I would like to query the table to make a new field in which the datetimes are rounded such that the seconds are set to zero, i.e. 29/07/2015 12:02:00. I want this so that i can sum values for records in each minute bin.

Hope you can help.

Thanks,

Chris

[CJ_London]

dates are (decimal) numbers, they just have the ability to be formatted to look like dates as we know them. The decimal part expresses time as a fraction of the number of seconds in the day (86400) - so 12 noon would be 0.5, 14:05 would be 0.5868055556

so depends on what you want to do, you could just format as 29/07/2015 12:02 (dd/mm/yyyy hh:nn)

or if you want to eliminate the seconds you can use the dateadd function - something like

dateadd("s",-datepart("s",mydate),mydate)

[SeaPorpoise]

Hi Ajax,

Thank you - the dateadd function does exactly what i need. And i learnt quite a lot working out how it does it...,

Chris

[SeaPorpoise]

Ah, is it possible the results this function is prone to rounding errors?

I have two records returned for most 1 min bins when i group by the new field.

[CJ_London]

regret I'm just going away for a few days so not time to look.

depends what you mean by rounding errors - your example rounded 12:02:42 to 12:02 which is what the function does - it deducts 42 seconds from the time. if datetime for 'myDate' is now() or some other calculated value, it is possible it is deducting 42 seconds from 43 seconds because time has moved on a fraction.

Suggest use the cdbl function to see the date as a decimal and compare values, perhaps you need to tweak it a bit

alternative is to use the format function I suggested (which returns a string) and group on that

[SeaPorpoise]

Thanks Ajax,

In fact, the formatting option works great. I didn't realise this output a text field - i thought it was just a display option and that group on that field would work on the underlying data. But, it works, so great - thanks,

Chris

[CJ_London]

the format property is a display option, the underlying value remains unchanged. The format function converts to text. Disadvantage of text is sorting values - text sorts result in a different order from numerical sorts. Try sorting

1
11
21
110

as text and you will get

1
11
110
21

You'll find this out if you try to sort

01/01/2016
02/01/2016
01/02/2016

as text you will get

01/01/2016
01/02/2016
02/01/2016

[SeaPorpoise]

I used the CDbl function and it looks like my datetime include decimal seconds. But the format function does the trick, and I can sort on the original datetime field.