Strange behaviour of epoch() when converting from Unix time

Stefan Manegold Stefan.Manegold at cwi.nl
Tue Apr 12 12:47:46 CEST 2016


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 at 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 at monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list

-- 
| Stefan.Manegold at CWI.nl | DB Architectures   (DA) |
| www.CWI.nl/~manegold/  | Science Park 123 (L321) |
| +31 (0)20 592-4212     | 1098 XG Amsterdam  (NL) |


More information about the users-list mailing list