Hi Marc,

appreantly, our epoch() function only works correctly when provided with an integer argument, but not when provided with a bigint argument (which is the canonical result type of the integer arithmetic:

sql>SELECT epoch(1460453860) AS "NOW", epoch(1460453860-300) AS "300 SECONDS AGO"; +----------------------------+----------------------------+ | NOW | 300 SECONDS AGO | +============================+============================+ | 2016-04-12 09:37:40.000000 | 1970-01-17 21:40:53.560000 | +----------------------------+----------------------------+ 1 tuple (1.318ms)

sql>SELECT epoch(1460453860) AS "NOW", epoch(1460453560) AS "300 SECONDS AGO"; +----------------------------+----------------------------+ | NOW | 300 SECONDS AGO | +============================+============================+ | 2016-04-12 09:37:40.000000 | 2016-04-12 09:32:40.000000 | +----------------------------+----------------------------+ 1 tuple (1.129ms)

sql>SELECT epoch(1460453860) AS "NOW", epoch(1460453860-300) AS "300 SECONDS AGO", 1460453860-300, epoch(1460453560); +----------------------------+----------------------------+------------+----------------------------+ | NOW | 300 SECONDS AGO | L1 | epoch_single_value | +============================+============================+============+============================+ | 2016-04-12 09:37:40.000000 | 1970-01-17 21:40:53.560000 | 1460453560 | 2016-04-12 09:32:40.000000 | +----------------------------+----------------------------+------------+----------------------------+ 1 tuple (1.124ms)

sql>\fraw

sql>SELECT epoch(1460453860) AS "NOW", epoch(1460453860-300) AS "300 SECONDS AGO", 1460453860-300, epoch(1460453560); % .L, .L, .L1, .L1 # table_name % NOW, "300 SECONDS AGO", L1, epoch_single_value # name % timestamp, timestamp, bigint, timestamp # type % 26, 26, 10, 26 # length [ 2016-04-12 09:37:40.000000, 1970-01-17 21:40:53.560000, 1460453560, 2016-04-12 09:32:40.000000 ]

sql>SELECT epoch(1460453860) AS "NOW", epoch(cast(1460453860-300 as int)) AS "300 SECONDS AGO", 1460453860-300, epoch(1460453560); % .L, .L1, .L2, .L2 # table_name % NOW, "300 SECONDS AGO", L2, epoch_single_value # name % timestamp, timestamp, bigint, timestamp # type % 26, 26, 10, 26 # length [ 2016-04-12 09:37:40.000000, 2016-04-12 09:32:40.000000, 1460453560, 2016-04-12 09:32:40.000000 ]

sql>\fsql

sql>SELECT epoch(1460453860) AS "NOW", epoch(cast(1460453860-300 as int)) AS "300 SECONDS AGO", 1460453860-300, epoch(1460453560); +----------------------------+----------------------------+------------+----------------------------+ | NOW | 300 SECONDS AGO | L2 | epoch_single_value | +============================+============================+============+============================+ | 2016-04-12 09:37:40.000000 | 2016-04-12 09:32:40.000000 | 1460453560 | 2016-04-12 09:32:40.000000 | +----------------------------+----------------------------+------------+----------------------------+ 1 tuple (1.358ms)

Please file a bug report via http://bugs.monetdb.org/

Best, Stefan

----- On Apr 12, 2016, at 11:46 AM, Marc Ballarin marc.ballarin@1und1.de wrote:

Hi,

I am observing a surprising behaviour of the epoch function:

The following works as expected: SELECT epoch(now()) AS "NOW", epoch(now() - INTERVAL '5' MINUTE) AS "5 MIN AGO"; +------------+------------+ | NOW | 5 MIN AGO | +============+============+ | 1460453860 | 1460453560 | +------------+------------+

The inverse does not: SELECT epoch(1460453860) AS "NOW", epoch(1460453860-300) AS "300 SECONDS AGO"; +----------------------------+----------------------------+ | NOW | 300 SECONDS AGO | +============================+============================+ | 2016-04-12 09:37:40.000000 | 1970-01-17 21:40:53.560000 | +----------------------------+----------------------------+

The "now" part is fine, the "300 seconds ago" part is obviously not what I expect.

Am I missing something or is this a bug?

Regards, Marc _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list