For things like this the ISO SQL spec states "the choice of whether to round or truncate is implementation-defined". That said, Oracle, Netezza, Vertica, and Postgres all round. Db2 truncates.
On Wed, Jan 23, 2019 at 12:26 PM Csaba Ringhofer <[email protected]> wrote: > Timestamps are often represented as ticks since some epoch, e.g. 1970.01.01 > 00:00:00, so negative timestamps make sense as times before the epoch - I > meant rounding vs truncating towards 0 vs rounding towards negative > infinite in this sense. Truncating towards negative infinity means that > timestamps are always truncated to an earlier timestamp. Truncating towards > 0 would mean that before 1970, timestamps are truncated upwards, which can > lead to similar troubles as the I ones mentioned with rounding. On x86 c++, > when a time_t is divided by an integer, the result is rounded towards 0, so > a naive implementation that uses time_t to represent timestamps can > truncate towards 0, but In impala::TimestampValue, time_ should be the > non-negative nanoseconds since midnight, so it can be simply divided with > 1000 to convert from nanoseconds to microseconds. >
