Offset timestamp field with random interval
Hello everyone! I am facing some (probably simple) problems. A snippet of a timestamp column in my table:
+----------------------------+ | ts | +============================+ | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 | +----------------------------+
I want to fill all the times in these fields with a random value (uniformly distributed is fine). This faces me with the following problems:
1) Adding an interval to a timestamp does not seem to be suited for dynamic intervals: This works fine: UPDATE "table" SET ts = (ts + INTERVAL '5000' SECOND); This doesn't: UPDATE "table" SET ts = (ts + INTERVAL (CAST(5000 AS STRING)) SECOND); Does someone maybe know of a way to accomplish this?
2) Does someone know how to normalize the output of the RAND operator available in SQL? It seems to calculate a random BIGINT, and in order for me to normalize it, I would need the maximum value of a BIGINT, which of course is platform dependent. Is there a way to get the max value of BIGINT on the SQL level? Or can I go about this another way?
All help is much appreciated!
Robin
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
On 20/10/15 16:51, Robin Cijvat wrote:
Hello everyone! I am facing some (probably simple) problems. A snippet of a timestamp column in my table:
+----------------------------+ | ts | +============================+ | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 | +----------------------------+
I want to fill all the times in these fields with a random value (uniformly distributed is fine). This faces me with the following problems:
- Adding an interval to a timestamp does not seem to be suited
for dynamic intervals: This works fine: UPDATE "table" SET ts = (ts
- INTERVAL '5000' SECOND); This doesn't: UPDATE "table" SET ts =
(ts + INTERVAL (CAST(5000 AS STRING)) SECOND); Does someone maybe know of a way to accomplish this?
- Does someone know how to normalize the output of the RAND
operator available in SQL? It seems to calculate a random BIGINT, and in order for me to normalize it, I would need the maximum value of a BIGINT, which of course is platform dependent. Is there a way to get the max value of BIGINT on the SQL level? Or can I go about this another way?
All help is much appreciated!
Robin
sql>select cast(5000 as interval second); +----------+ | L1 | +==========+ | 5000.000 | +----------+
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
- -- Sjoerd Mullender
(In MonetDB,) BIGINT is a 64-bit signed integer, independent of the hardware/OS platform; hence, the largest value is 2^63-1 (the smallest value in MonetDB is -(2^63-1)); see also https://www.monetdb.org/wiki/MonetDB_type_system
Stefan
----- On Oct 20, 2015, at 5:55 PM, Sjoerd Mullender sjoerd@acm.org wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
On 20/10/15 16:51, Robin Cijvat wrote:
Hello everyone! I am facing some (probably simple) problems. A snippet of a timestamp column in my table:
+----------------------------+ | ts | +============================+ | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 | +----------------------------+
I want to fill all the times in these fields with a random value (uniformly distributed is fine). This faces me with the following problems:
- Adding an interval to a timestamp does not seem to be suited
for dynamic intervals: This works fine: UPDATE "table" SET ts = (ts
- INTERVAL '5000' SECOND); This doesn't: UPDATE "table" SET ts =
(ts + INTERVAL (CAST(5000 AS STRING)) SECOND); Does someone maybe know of a way to accomplish this?
- Does someone know how to normalize the output of the RAND
operator available in SQL? It seems to calculate a random BIGINT, and in order for me to normalize it, I would need the maximum value of a BIGINT, which of course is platform dependent. Is there a way to get the max value of BIGINT on the SQL level? Or can I go about this another way?
All help is much appreciated!
Robin
sql>select cast(5000 as interval second); +----------+ | L1 | +==========+ | 5000.000 | +----------+
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWJmPoAAoJEISMxT6LrWYgCzkH/jm6+xRYh93Om78w5UZTEV84 E9aBApWr3arH9QNAmqrQf0PvHcdh3g/nDTiEktQeMLcm8u4+d62G6KS4m19xWy71 KiI6LTtFuMpv3Ax9WN7U7ji95w4/XEcuooW7RBqHWHbPo8FooAwJSxHxWoSKXQ/6 49nHZRO5lD3Ydfnse/mMH32irIMau3lBgsX/cCptZkxpsthNspfpuQ7CsrmTF0EZ BK/NkQRymNRmLnDeA9yYs+HCGSEdNrB/cYoAb9CDkcwYbrgbbvWcTJewahZsX+TR QlKuDvvldhVtIpJCAsHQ/vksGpqmoVAM3hl/S5JQ1FDzZPyKQAQjaw/Ze7s6iy0= =vG5t -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi Stefan and Sjoerd, thanks for the help! By the way, I found out that rand() returns an integer, which also seems to be the same across platforms, so I wrote the following SQL, which almost works:
UPDATE "table" SET ts = (ts + CAST( CAST((RAND()/2147483647.0*24*60*60) AS INT) AS INTERVAL SECOND));
The problem here is that the expression only gets evaluated once, hence I end up with the same 'random' addition in all tuples. Is there any way I could trick MonetDB into not reusing the evaluated expression?
On 20-10-15 23:41, Stefan Manegold wrote:
(In MonetDB,) BIGINT is a 64-bit signed integer, independent of the hardware/OS platform; hence, the largest value is 2^63-1 (the smallest value in MonetDB is -(2^63-1)); see also https://www.monetdb.org/wiki/MonetDB_type_system
Stefan
----- On Oct 20, 2015, at 5:55 PM, Sjoerd Mullender sjoerd@acm.org wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
On 20/10/15 16:51, Robin Cijvat wrote:
Hello everyone! I am facing some (probably simple) problems. A snippet of a timestamp column in my table:
+----------------------------+ | ts | +============================+ | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 | +----------------------------+
I want to fill all the times in these fields with a random value (uniformly distributed is fine). This faces me with the following problems:
- Adding an interval to a timestamp does not seem to be suited
for dynamic intervals: This works fine: UPDATE "table" SET ts = (ts
- INTERVAL '5000' SECOND); This doesn't: UPDATE "table" SET ts =
(ts + INTERVAL (CAST(5000 AS STRING)) SECOND); Does someone maybe know of a way to accomplish this?
- Does someone know how to normalize the output of the RAND
operator available in SQL? It seems to calculate a random BIGINT, and in order for me to normalize it, I would need the maximum value of a BIGINT, which of course is platform dependent. Is there a way to get the max value of BIGINT on the SQL level? Or can I go about this another way?
All help is much appreciated!
Robin
sql>select cast(5000 as interval second); +----------+ | L1 | +==========+ | 5000.000 | +----------+
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWJmPoAAoJEISMxT6LrWYgCzkH/jm6+xRYh93Om78w5UZTEV84 E9aBApWr3arH9QNAmqrQf0PvHcdh3g/nDTiEktQeMLcm8u4+d62G6KS4m19xWy71 KiI6LTtFuMpv3Ax9WN7U7ji95w4/XEcuooW7RBqHWHbPo8FooAwJSxHxWoSKXQ/6 49nHZRO5lD3Ydfnse/mMH32irIMau3lBgsX/cCptZkxpsthNspfpuQ7CsrmTF0EZ BK/NkQRymNRmLnDeA9yYs+HCGSEdNrB/cYoAb9CDkcwYbrgbbvWcTJewahZsX+TR QlKuDvvldhVtIpJCAsHQ/vksGpqmoVAM3hl/S5JQ1FDzZPyKQAQjaw/Ze7s6iy0= =vG5t -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi Robin,
CAST( CAST((RAND()/2147483647.0*24*60*60) AS INT) AS INTERVAL SECOND)
is an expression that does not depend on the table data; hence, it's assumed to be constant and evaluated only once.
The solution would be to make this expression (look as if it is) dependent on the data (i.e., ts). E.g., one could change the SQL front-end or MAL optimizers (CSE?) to not treat rand() as a "constant" (data-independent function call), or one could make rand() accept (and ignore) an optional argument (of any type?); then rand(ts) should be evaluated with each row.
Alternative: ALTER TABLE "table" ADD COLUMN "r" INT DEFAULT RAND(); UPDATE "table" SET ts = (ts + CAST( CAST(("r"/2147483647.0*24*60*60) AS INT) AS INTERVAL SECOND)); (ALTER TABLE "table" DROP COLUMN "r";)
Stefan
----- On Oct 21, 2015, at 7:13 AM, Robin Cijvat robin.cijvat@monetdbsolutions.com wrote:
Hi Stefan and Sjoerd, thanks for the help! By the way, I found out that rand() returns an integer, which also seems to be the same across platforms, so I wrote the following SQL, which almost works:
UPDATE "table" SET ts = (ts + CAST( CAST((RAND()/2147483647.0*24*60*60) AS INT) AS INTERVAL SECOND));
The problem here is that the expression only gets evaluated once, hence I end up with the same 'random' addition in all tuples. Is there any way I could trick MonetDB into not reusing the evaluated expression?
On 20-10-15 23:41, Stefan Manegold wrote:
(In MonetDB,) BIGINT is a 64-bit signed integer, independent of the hardware/OS platform; hence, the largest value is 2^63-1 (the smallest value in MonetDB is -(2^63-1)); see also https://www.monetdb.org/wiki/MonetDB_type_system
Stefan
----- On Oct 20, 2015, at 5:55 PM, Sjoerd Mullender sjoerd@acm.org wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
On 20/10/15 16:51, Robin Cijvat wrote:
Hello everyone! I am facing some (probably simple) problems. A snippet of a timestamp column in my table:
+----------------------------+ | ts | +============================+ | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 | +----------------------------+
I want to fill all the times in these fields with a random value (uniformly distributed is fine). This faces me with the following problems:
- Adding an interval to a timestamp does not seem to be suited
for dynamic intervals: This works fine: UPDATE "table" SET ts = (ts
- INTERVAL '5000' SECOND); This doesn't: UPDATE "table" SET ts =
(ts + INTERVAL (CAST(5000 AS STRING)) SECOND); Does someone maybe know of a way to accomplish this?
- Does someone know how to normalize the output of the RAND
operator available in SQL? It seems to calculate a random BIGINT, and in order for me to normalize it, I would need the maximum value of a BIGINT, which of course is platform dependent. Is there a way to get the max value of BIGINT on the SQL level? Or can I go about this another way?
All help is much appreciated!
Robin
sql>select cast(5000 as interval second); +----------+ | L1 | +==========+ | 5000.000 | +----------+
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWJmPoAAoJEISMxT6LrWYgCzkH/jm6+xRYh93Om78w5UZTEV84 E9aBApWr3arH9QNAmqrQf0PvHcdh3g/nDTiEktQeMLcm8u4+d62G6KS4m19xWy71 KiI6LTtFuMpv3Ax9WN7U7ji95w4/XEcuooW7RBqHWHbPo8FooAwJSxHxWoSKXQ/6 49nHZRO5lD3Ydfnse/mMH32irIMau3lBgsX/cCptZkxpsthNspfpuQ7CsrmTF0EZ BK/NkQRymNRmLnDeA9yYs+HCGSEdNrB/cYoAb9CDkcwYbrgbbvWcTJewahZsX+TR QlKuDvvldhVtIpJCAsHQ/vksGpqmoVAM3hl/S5JQ1FDzZPyKQAQjaw/Ze7s6iy0= =vG5t -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi,
Indeed, rand is an 'unsafe' function and should not be optimized away by the CSE.
On 21/10/15 08:25, Stefan Manegold wrote:
Hi Robin,
CAST( CAST((RAND()/2147483647.0*24*60*60) AS INT) AS INTERVAL SECOND)
is an expression that does not depend on the table data; hence, it's assumed to be constant and evaluated only once.
The solution would be to make this expression (look as if it is) dependent on the data (i.e., ts). E.g., one could change the SQL front-end or MAL optimizers (CSE?) to not treat rand() as a "constant" (data-independent function call), or one could make rand() accept (and ignore) an optional argument (of any type?); then rand(ts) should be evaluated with each row.
Alternative: ALTER TABLE "table" ADD COLUMN "r" INT DEFAULT RAND(); UPDATE "table" SET ts = (ts + CAST( CAST(("r"/2147483647.0*24*60*60) AS INT) AS INTERVAL SECOND)); (ALTER TABLE "table" DROP COLUMN "r";)
Stefan
----- On Oct 21, 2015, at 7:13 AM, Robin Cijvat robin.cijvat@monetdbsolutions.com wrote:
Hi Stefan and Sjoerd, thanks for the help! By the way, I found out that rand() returns an integer, which also seems to be the same across platforms, so I wrote the following SQL, which almost works:
UPDATE "table" SET ts = (ts + CAST( CAST((RAND()/2147483647.0*24*60*60) AS INT) AS INTERVAL SECOND));
The problem here is that the expression only gets evaluated once, hence I end up with the same 'random' addition in all tuples. Is there any way I could trick MonetDB into not reusing the evaluated expression?
On 20-10-15 23:41, Stefan Manegold wrote:
(In MonetDB,) BIGINT is a 64-bit signed integer, independent of the hardware/OS platform; hence, the largest value is 2^63-1 (the smallest value in MonetDB is -(2^63-1)); see also https://www.monetdb.org/wiki/MonetDB_type_system
Stefan
----- On Oct 20, 2015, at 5:55 PM, Sjoerd Mullender sjoerd@acm.org wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
On 20/10/15 16:51, Robin Cijvat wrote:
Hello everyone! I am facing some (probably simple) problems. A snippet of a timestamp column in my table:
+----------------------------+ | ts | +============================+ | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 | +----------------------------+
I want to fill all the times in these fields with a random value (uniformly distributed is fine). This faces me with the following problems:
- Adding an interval to a timestamp does not seem to be suited
for dynamic intervals: This works fine: UPDATE "table" SET ts = (ts
- INTERVAL '5000' SECOND); This doesn't: UPDATE "table" SET ts =
(ts + INTERVAL (CAST(5000 AS STRING)) SECOND); Does someone maybe know of a way to accomplish this?
- Does someone know how to normalize the output of the RAND
operator available in SQL? It seems to calculate a random BIGINT, and in order for me to normalize it, I would need the maximum value of a BIGINT, which of course is platform dependent. Is there a way to get the max value of BIGINT on the SQL level? Or can I go about this another way?
All help is much appreciated!
Robin
sql>select cast(5000 as interval second); +----------+ | L1 | +==========+ | 5000.000 | +----------+
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWJmPoAAoJEISMxT6LrWYgCzkH/jm6+xRYh93Om78w5UZTEV84 E9aBApWr3arH9QNAmqrQf0PvHcdh3g/nDTiEktQeMLcm8u4+d62G6KS4m19xWy71 KiI6LTtFuMpv3Ax9WN7U7ji95w4/XEcuooW7RBqHWHbPo8FooAwJSxHxWoSKXQ/6 49nHZRO5lD3Ydfnse/mMH32irIMau3lBgsX/cCptZkxpsthNspfpuQ7CsrmTF0EZ BK/NkQRymNRmLnDeA9yYs+HCGSEdNrB/cYoAb9CDkcwYbrgbbvWcTJewahZsX+TR QlKuDvvldhVtIpJCAsHQ/vksGpqmoVAM3hl/S5JQ1FDzZPyKQAQjaw/Ze7s6iy0= =vG5t -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
You also might want to file a but report:
rand() seems to be treated a "constant" only when in an expression; see below ...
Stefan
sql>create table t (a int, b int default rand()); operation successful (8.297ms) sql>insert into t(a) values (1); 1 affected row (5.830ms) sql>insert into t(a) values (2); 1 affected row (5.175ms) sql>insert into t(a) values (3); 1 affected row (5.258ms) sql>alter table t add column c int default rand(); operation successful (11.170ms) sql>alter table t add column d int default null; operation successful (10.695ms) sql>select * from t; +------+------------+------------+------+ | a | b | c | d | +======+============+============+======+ | 1 | 2018667017 | 1223444628 | null | | 2 | 1184696955 | 1855771568 | null | | 3 | 758492986 | 1856268030 | null | +------+------------+------------+------+ 3 tuples (1.891ms)
sql>update t set d = rand(); -- works as expected 3 affected rows (7.684ms) sql>select * from t; +------+------------+------------+------------+ | a | b | c | d | +======+============+============+============+ | 1 | 2018667017 | 1223444628 | 2132096871 | | 2 | 1184696955 | 1855771568 | 465839912 | | 3 | 758492986 | 1856268030 | 1856154042 | +------+------------+------------+------------+ 3 tuples (2.123ms)
sql>update t set a = a + rand(); -- does not work as expected/supposed to 3 affected rows (7.515ms) sql>select * from t; +----------+------------+------------+------------+ | a | b | c | d | +==========+============+============+============+ | 12809903 | 2018667017 | 1223444628 | 2132096871 | | 12809904 | 1184696955 | 1855771568 | 465839912 | | 12809905 | 758492986 | 1856268030 | 1856154042 | +----------+------------+------------+------------+ 3 tuples (3.547ms)
----- On Oct 21, 2015, at 8:25 AM, Stefan Manegold Stefan.Manegold@cwi.nl wrote:
Hi Robin,
CAST( CAST((RAND()/2147483647.0*24*60*60) AS INT) AS INTERVAL SECOND)
is an expression that does not depend on the table data; hence, it's assumed to be constant and evaluated only once.
The solution would be to make this expression (look as if it is) dependent on the data (i.e., ts). E.g., one could change the SQL front-end or MAL optimizers (CSE?) to not treat rand() as a "constant" (data-independent function call), or one could make rand() accept (and ignore) an optional argument (of any type?); then rand(ts) should be evaluated with each row.
Alternative: ALTER TABLE "table" ADD COLUMN "r" INT DEFAULT RAND(); UPDATE "table" SET ts = (ts + CAST( CAST(("r"/2147483647.0*24*60*60) AS INT) AS INTERVAL SECOND)); (ALTER TABLE "table" DROP COLUMN "r";)
Stefan
----- On Oct 21, 2015, at 7:13 AM, Robin Cijvat robin.cijvat@monetdbsolutions.com wrote:
Hi Stefan and Sjoerd, thanks for the help! By the way, I found out that rand() returns an integer, which also seems to be the same across platforms, so I wrote the following SQL, which almost works:
UPDATE "table" SET ts = (ts + CAST( CAST((RAND()/2147483647.0*24*60*60) AS INT) AS INTERVAL SECOND));
The problem here is that the expression only gets evaluated once, hence I end up with the same 'random' addition in all tuples. Is there any way I could trick MonetDB into not reusing the evaluated expression?
On 20-10-15 23:41, Stefan Manegold wrote:
(In MonetDB,) BIGINT is a 64-bit signed integer, independent of the hardware/OS platform; hence, the largest value is 2^63-1 (the smallest value in MonetDB is -(2^63-1)); see also https://www.monetdb.org/wiki/MonetDB_type_system
Stefan
----- On Oct 20, 2015, at 5:55 PM, Sjoerd Mullender sjoerd@acm.org wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
On 20/10/15 16:51, Robin Cijvat wrote:
Hello everyone! I am facing some (probably simple) problems. A snippet of a timestamp column in my table:
+----------------------------+ | ts | +============================+ | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 | +----------------------------+
I want to fill all the times in these fields with a random value (uniformly distributed is fine). This faces me with the following problems:
- Adding an interval to a timestamp does not seem to be suited
for dynamic intervals: This works fine: UPDATE "table" SET ts = (ts
- INTERVAL '5000' SECOND); This doesn't: UPDATE "table" SET ts =
(ts + INTERVAL (CAST(5000 AS STRING)) SECOND); Does someone maybe know of a way to accomplish this?
- Does someone know how to normalize the output of the RAND
operator available in SQL? It seems to calculate a random BIGINT, and in order for me to normalize it, I would need the maximum value of a BIGINT, which of course is platform dependent. Is there a way to get the max value of BIGINT on the SQL level? Or can I go about this another way?
All help is much appreciated!
Robin
sql>select cast(5000 as interval second); +----------+ | L1 | +==========+ | 5000.000 | +----------+
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWJmPoAAoJEISMxT6LrWYgCzkH/jm6+xRYh93Om78w5UZTEV84 E9aBApWr3arH9QNAmqrQf0PvHcdh3g/nDTiEktQeMLcm8u4+d62G6KS4m19xWy71 KiI6LTtFuMpv3Ax9WN7U7ji95w4/XEcuooW7RBqHWHbPo8FooAwJSxHxWoSKXQ/6 49nHZRO5lD3Ydfnse/mMH32irIMau3lBgsX/cCptZkxpsthNspfpuQ7CsrmTF0EZ BK/NkQRymNRmLnDeA9yYs+HCGSEdNrB/cYoAb9CDkcwYbrgbbvWcTJewahZsX+TR QlKuDvvldhVtIpJCAsHQ/vksGpqmoVAM3hl/S5JQ1FDzZPyKQAQjaw/Ze7s6iy0= =vG5t -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
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) |
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
The range of RAND is system dependent. On Linux (glibc) it's 0 - 2^63-1, but on Windows it's only 0 - 2^31-1. I don't know for other platforms.
On 20/10/15 23:41, Stefan Manegold wrote:
(In MonetDB,) BIGINT is a 64-bit signed integer, independent of the hardware/OS platform; hence, the largest value is 2^63-1 (the smallest value in MonetDB is -(2^63-1)); see also https://www.monetdb.org/wiki/MonetDB_type_system
Stefan
----- On Oct 20, 2015, at 5:55 PM, Sjoerd Mullender sjoerd@acm.org wrote:
On 20/10/15 16:51, Robin Cijvat wrote:
Hello everyone! I am facing some (probably simple) problems. A snippet of a timestamp column in my table:
+----------------------------+ | ts | +============================+ | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 | +----------------------------+
I want to fill all the times in these fields with a random value (uniformly distributed is fine). This faces me with the following problems:
- Adding an interval to a timestamp does not seem to be
suited for dynamic intervals: This works fine: UPDATE "table" SET ts = (ts + INTERVAL '5000' SECOND); This doesn't: UPDATE "table" SET ts = (ts + INTERVAL (CAST(5000 AS STRING)) SECOND); Does someone maybe know of a way to accomplish this?
- Does someone know how to normalize the output of the RAND
operator available in SQL? It seems to calculate a random BIGINT, and in order for me to normalize it, I would need the maximum value of a BIGINT, which of course is platform dependent. Is there a way to get the max value of BIGINT on the SQL level? Or can I go about this another way?
All help is much appreciated!
Robin
sql>select cast(5000 as interval second); +----------+ | L1 | +==========+ | 5000.000 | +----------+
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
- -- Sjoerd Mullender
Thanks for all your help and suggestions! I will file a bug report and use the workaround proposed by Stefan for now.
@Sjoerd: thanks for the info, that means I cannot just hard code a value to divide the RAND output by to normalize.
Does anyone know of a way to get the maximum returned value by a call to RAND() in SQL?
On 21-10-15 09:13, Sjoerd Mullender wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
The range of RAND is system dependent. On Linux (glibc) it's 0 - 2^63-1, but on Windows it's only 0 - 2^31-1. I don't know for other platforms.
On 20/10/15 23:41, Stefan Manegold wrote:
(In MonetDB,) BIGINT is a 64-bit signed integer, independent of the hardware/OS platform; hence, the largest value is 2^63-1 (the smallest value in MonetDB is -(2^63-1)); see also https://www.monetdb.org/wiki/MonetDB_type_system
Stefan
----- On Oct 20, 2015, at 5:55 PM, Sjoerd Mullender sjoerd@acm.org wrote:
On 20/10/15 16:51, Robin Cijvat wrote:
Hello everyone! I am facing some (probably simple) problems. A snippet of a timestamp column in my table:
+----------------------------+ | ts | +============================+ | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 | +----------------------------+
I want to fill all the times in these fields with a random value (uniformly distributed is fine). This faces me with the following problems:
- Adding an interval to a timestamp does not seem to be
suited for dynamic intervals: This works fine: UPDATE "table" SET ts = (ts + INTERVAL '5000' SECOND); This doesn't: UPDATE "table" SET ts = (ts + INTERVAL (CAST(5000 AS STRING)) SECOND); Does someone maybe know of a way to accomplish this?
- Does someone know how to normalize the output of the RAND
operator available in SQL? It seems to calculate a random BIGINT, and in order for me to normalize it, I would need the maximum value of a BIGINT, which of course is platform dependent. Is there a way to get the max value of BIGINT on the SQL level? Or can I go about this another way?
All help is much appreciated!
Robin
sql>select cast(5000 as interval second); +----------+ | L1 | +==========+ | 5000.000 | +----------+
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWJzr8AAoJEISMxT6LrWYghCwIAL7QiRsOmorYkxXDSnT686iJ zn9Wco//qf0xWaGKHMimhXP0LWX9SN1rJSOckZXaFYC+mTdumhzBgRwSop46cNYZ u+xoRzoqYoKpTcEjZekJ97pjMADUU3np6sUbx0lwbr1ELHYJ08KmLJk7gxF9YRxp UGjsMt7kJQ5rnvfFlodOPuNZEj1uG5KDR92eQTP59wt5d9NweiKaPvntSuJWp78W aiOlkdgHtqEjn1qMvwTwQvxGkKQrW/q+Q9W9prc9WVIUc6WOaM/KOuS1JKkWe1Kp wqVbbsTOYekyH4xxOmLjpyJnO7p+qNZYyX4xs7+fHSRTnUjFBN+A3qQWIaSAYrA= =Lspw -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
MonetDB's SQL RAND() function returns type int:
sql>select * from functions where name = 'rand'; +------+------+---------+-------+----------+------+-------------+--------+--------+-----------+ | id | name | func | mod | language | type | side_effect | varres | vararg | schema_id | +======+======+=========+=======+==========+======+=============+========+========+===========+ | 867 | rand | rand | mmath | 0 | 1 | true | false | false | 0 | | 868 | rand | sqlrand | mmath | 0 | 1 | true | false | false | 0 | +------+------+---------+-------+----------+------+-------------+--------+--------+-----------+ 2 tuples (3.151ms) sql>\fraw sql>select rand(); % .L # table_name % rand # name % int # type % 9 # length [ 563202665 ]
i.e., the max possible value is the minimum of 2^31-1 and the max value that the underlying systems's (libc's) rand() function returns (for Linux and Windows see what Sjoerd wrote).
----- On Oct 21, 2015, at 10:19 AM, Robin Cijvat robin.cijvat@monetdbsolutions.com wrote:
Thanks for all your help and suggestions! I will file a bug report and use the workaround proposed by Stefan for now.
@Sjoerd: thanks for the info, that means I cannot just hard code a value to divide the RAND output by to normalize.
Does anyone know of a way to get the maximum returned value by a call to RAND() in SQL?
On 21-10-15 09:13, Sjoerd Mullender wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
The range of RAND is system dependent. On Linux (glibc) it's 0 - 2^63-1, but on Windows it's only 0 - 2^31-1. I don't know for other platforms.
On 20/10/15 23:41, Stefan Manegold wrote:
(In MonetDB,) BIGINT is a 64-bit signed integer, independent of the hardware/OS platform; hence, the largest value is 2^63-1 (the smallest value in MonetDB is -(2^63-1)); see also https://www.monetdb.org/wiki/MonetDB_type_system
Stefan
----- On Oct 20, 2015, at 5:55 PM, Sjoerd Mullender sjoerd@acm.org wrote:
On 20/10/15 16:51, Robin Cijvat wrote:
Hello everyone! I am facing some (probably simple) problems. A snippet of a timestamp column in my table:
+----------------------------+ | ts | +============================+ | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 | +----------------------------+
I want to fill all the times in these fields with a random value (uniformly distributed is fine). This faces me with the following problems:
- Adding an interval to a timestamp does not seem to be
suited for dynamic intervals: This works fine: UPDATE "table" SET ts = (ts + INTERVAL '5000' SECOND); This doesn't: UPDATE "table" SET ts = (ts + INTERVAL (CAST(5000 AS STRING)) SECOND); Does someone maybe know of a way to accomplish this?
- Does someone know how to normalize the output of the RAND
operator available in SQL? It seems to calculate a random BIGINT, and in order for me to normalize it, I would need the maximum value of a BIGINT, which of course is platform dependent. Is there a way to get the max value of BIGINT on the SQL level? Or can I go about this another way?
All help is much appreciated!
Robin
sql>select cast(5000 as interval second); +----------+ | L1 | +==========+ | 5000.000 | +----------+
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWJzr8AAoJEISMxT6LrWYghCwIAL7QiRsOmorYkxXDSnT686iJ zn9Wco//qf0xWaGKHMimhXP0LWX9SN1rJSOckZXaFYC+mTdumhzBgRwSop46cNYZ u+xoRzoqYoKpTcEjZekJ97pjMADUU3np6sUbx0lwbr1ELHYJ08KmLJk7gxF9YRxp UGjsMt7kJQ5rnvfFlodOPuNZEj1uG5KDR92eQTP59wt5d9NweiKaPvntSuJWp78W aiOlkdgHtqEjn1qMvwTwQvxGkKQrW/q+Q9W9prc9WVIUc6WOaM/KOuS1JKkWe1Kp wqVbbsTOYekyH4xxOmLjpyJnO7p+qNZYyX4xs7+fHSRTnUjFBN+A3qQWIaSAYrA= =Lspw -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Yes, but is there a way I could fetch this maximum value within SQL? Without having to know what system I am running on?
On 21-10-15 10:31, Stefan Manegold wrote:
MonetDB's SQL RAND() function returns type int:
sql>select * from functions where name = 'rand'; +------+------+---------+-------+----------+------+-------------+--------+--------+-----------+ | id | name | func | mod | language | type | side_effect | varres | vararg | schema_id | +======+======+=========+=======+==========+======+=============+========+========+===========+ | 867 | rand | rand | mmath | 0 | 1 | true | false | false | 0 | | 868 | rand | sqlrand | mmath | 0 | 1 | true | false | false | 0 | +------+------+---------+-------+----------+------+-------------+--------+--------+-----------+ 2 tuples (3.151ms) sql>\fraw sql>select rand(); % .L # table_name % rand # name % int # type % 9 # length [ 563202665 ]
i.e., the max possible value is the minimum of 2^31-1 and the max value that the underlying systems's (libc's) rand() function returns (for Linux and Windows see what Sjoerd wrote).
----- On Oct 21, 2015, at 10:19 AM, Robin Cijvat robin.cijvat@monetdbsolutions.com wrote:
Thanks for all your help and suggestions! I will file a bug report and use the workaround proposed by Stefan for now.
@Sjoerd: thanks for the info, that means I cannot just hard code a value to divide the RAND output by to normalize.
Does anyone know of a way to get the maximum returned value by a call to RAND() in SQL?
On 21-10-15 09:13, Sjoerd Mullender wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
The range of RAND is system dependent. On Linux (glibc) it's 0 - 2^63-1, but on Windows it's only 0 - 2^31-1. I don't know for other platforms.
On 20/10/15 23:41, Stefan Manegold wrote:
(In MonetDB,) BIGINT is a 64-bit signed integer, independent of the hardware/OS platform; hence, the largest value is 2^63-1 (the smallest value in MonetDB is -(2^63-1)); see also https://www.monetdb.org/wiki/MonetDB_type_system
Stefan
----- On Oct 20, 2015, at 5:55 PM, Sjoerd Mullender sjoerd@acm.org wrote:
On 20/10/15 16:51, Robin Cijvat wrote:
> Hello everyone! I am facing some (probably simple) problems. > A snippet of a timestamp column in my table: > > +----------------------------+ | ts > | +============================+ | 1595-04-02 00:00:00.000000 > | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 > | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 > | +----------------------------+ > > I want to fill all the times in these fields with a random > value (uniformly distributed is fine). This faces me with the > following problems: > > 1) Adding an interval to a timestamp does not seem to be > suited for dynamic intervals: This works fine: UPDATE "table" > SET ts = (ts + INTERVAL '5000' SECOND); This doesn't: UPDATE > "table" SET ts = (ts + INTERVAL (CAST(5000 AS STRING)) > SECOND); Does someone maybe know of a way to accomplish > this? > > 2) Does someone know how to normalize the output of the RAND > operator available in SQL? It seems to calculate a random > BIGINT, and in order for me to normalize it, I would need the > maximum value of a BIGINT, which of course is platform > dependent. Is there a way to get the max value of BIGINT on > the SQL level? Or can I go about this another way? > > > All help is much appreciated! > > Robin
sql>select cast(5000 as interval second); +----------+ | L1 | +==========+ | 5000.000 | +----------+
> > > _______________________________________________ users-list > mailing list users-list@monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWJzr8AAoJEISMxT6LrWYghCwIAL7QiRsOmorYkxXDSnT686iJ zn9Wco//qf0xWaGKHMimhXP0LWX9SN1rJSOckZXaFYC+mTdumhzBgRwSop46cNYZ u+xoRzoqYoKpTcEjZekJ97pjMADUU3np6sUbx0lwbr1ELHYJ08KmLJk7gxF9YRxp UGjsMt7kJQ5rnvfFlodOPuNZEj1uG5KDR92eQTP59wt5d9NweiKaPvntSuJWp78W aiOlkdgHtqEjn1qMvwTwQvxGkKQrW/q+Q9W9prc9WVIUc6WOaM/KOuS1JKkWe1Kp wqVbbsTOYekyH4xxOmLjpyJnO7p+qNZYyX4xs7+fHSRTnUjFBN+A3qQWIaSAYrA= =Lspw -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
In SQL standard? I don't know.
In MonetDB's SQL implementation: not right now (AFAIK).
We/you could consider exposing libc macro RAND_MAX as standard SQL constant (variable?) --- provided it is indeed available on all platforms ... ?
Or you just assume (or better double-check) that rand() on most/all "standard" systems does (at least) produce random values between 0 & 2^31-1 ...
Stefan
----- On Oct 21, 2015, at 10:40 AM, Robin Cijvat robin.cijvat@monetdbsolutions.com wrote:
Yes, but is there a way I could fetch this maximum value within SQL? Without having to know what system I am running on?
On 21-10-15 10:31, Stefan Manegold wrote:
MonetDB's SQL RAND() function returns type int:
sql>select * from functions where name = 'rand'; +------+------+---------+-------+----------+------+-------------+--------+--------+-----------+ | id | name | func | mod | language | type | side_effect | varres | vararg | | schema_id | +======+======+=========+=======+==========+======+=============+========+========+===========+ | 867 | rand | rand | mmath | 0 | 1 | true | false | false | | 0 | | 868 | rand | sqlrand | mmath | 0 | 1 | true | false | false | | 0 | +------+------+---------+-------+----------+------+-------------+--------+--------+-----------+ 2 tuples (3.151ms) sql>\fraw sql>select rand(); % .L # table_name % rand # name % int # type % 9 # length [ 563202665 ]
i.e., the max possible value is the minimum of 2^31-1 and the max value that the underlying systems's (libc's) rand() function returns (for Linux and Windows see what Sjoerd wrote).
----- On Oct 21, 2015, at 10:19 AM, Robin Cijvat robin.cijvat@monetdbsolutions.com wrote:
Thanks for all your help and suggestions! I will file a bug report and use the workaround proposed by Stefan for now.
@Sjoerd: thanks for the info, that means I cannot just hard code a value to divide the RAND output by to normalize.
Does anyone know of a way to get the maximum returned value by a call to RAND() in SQL?
On 21-10-15 09:13, Sjoerd Mullender wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
The range of RAND is system dependent. On Linux (glibc) it's 0 - 2^63-1, but on Windows it's only 0 - 2^31-1. I don't know for other platforms.
On 20/10/15 23:41, Stefan Manegold wrote:
(In MonetDB,) BIGINT is a 64-bit signed integer, independent of the hardware/OS platform; hence, the largest value is 2^63-1 (the smallest value in MonetDB is -(2^63-1)); see also https://www.monetdb.org/wiki/MonetDB_type_system
Stefan
----- On Oct 20, 2015, at 5:55 PM, Sjoerd Mullender sjoerd@acm.org wrote:
On 20/10/15 16:51, Robin Cijvat wrote:
>> Hello everyone! I am facing some (probably simple) problems. >> A snippet of a timestamp column in my table: >> >> +----------------------------+ | ts >> | +============================+ | 1595-04-02 00:00:00.000000 >> | | 1595-04-02 00:00:00.000000 | | 1595-04-02 00:00:00.000000 >> | | 1595-04-02 00:00:00.000000 | | 1598-03-25 00:00:00.000000 >> | +----------------------------+ >> >> I want to fill all the times in these fields with a random >> value (uniformly distributed is fine). This faces me with the >> following problems: >> >> 1) Adding an interval to a timestamp does not seem to be >> suited for dynamic intervals: This works fine: UPDATE "table" >> SET ts = (ts + INTERVAL '5000' SECOND); This doesn't: UPDATE >> "table" SET ts = (ts + INTERVAL (CAST(5000 AS STRING)) >> SECOND); Does someone maybe know of a way to accomplish >> this? >> >> 2) Does someone know how to normalize the output of the RAND >> operator available in SQL? It seems to calculate a random >> BIGINT, and in order for me to normalize it, I would need the >> maximum value of a BIGINT, which of course is platform >> dependent. Is there a way to get the max value of BIGINT on >> the SQL level? Or can I go about this another way? >> >> >> All help is much appreciated! >> >> Robin
sql>select cast(5000 as interval second); +----------+ | L1 | +==========+ | 5000.000 | +----------+
>> >> >> _______________________________________________ users-list >> mailing list users-list@monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWJzr8AAoJEISMxT6LrWYghCwIAL7QiRsOmorYkxXDSnT686iJ zn9Wco//qf0xWaGKHMimhXP0LWX9SN1rJSOckZXaFYC+mTdumhzBgRwSop46cNYZ u+xoRzoqYoKpTcEjZekJ97pjMADUU3np6sUbx0lwbr1ELHYJ08KmLJk7gxF9YRxp UGjsMt7kJQ5rnvfFlodOPuNZEj1uG5KDR92eQTP59wt5d9NweiKaPvntSuJWp78W aiOlkdgHtqEjn1qMvwTwQvxGkKQrW/q+Q9W9prc9WVIUc6WOaM/KOuS1JKkWe1Kp wqVbbsTOYekyH4xxOmLjpyJnO7p+qNZYyX4xs7+fHSRTnUjFBN+A3qQWIaSAYrA= =Lspw -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (4)
-
Martin Kersten -
Robin Cijvat -
Sjoerd Mullender -
Stefan Manegold