How do I group by day when the date is stored as a long?

Question

I need to group a query by day but the date is stored as a long, how do I do that with SQL?

I need this to work across multiple vendors - oracle, db2, sql-server & mysql.

The long is milliseconds, generated by java.util.Date.getTime() - which I can't change.

Answer 1

Divide the date column by the number of base time units per day, rounding down. For example, if your base unit is seconds, then you must divide the date column by 24*60*60 = 86400. Use the resulting value in the group by.

Note that this will fail if your time source is synchronized with astronomical time (say, your system clock is synchronized with NTP) because in this case, every few years a leap-second is added. If this is the case (and it usually is) and you must make sure that the result is exact, then you must convert the date column to a string or a DB supported date format.

This can mean to add another column or a view.