Strange behaviour of epoch() when converting from Unix time
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
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
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
participants (2)
-
Marc Ballarin -
Stefan Manegold