Problem selecting entries newer than a certain date
Hi everyone,
I’m probably missing something, but it seems that I cannot select entries that are newer than a given timestamp.
Below are my attempts and the schema of the table. Any help would be greatly appreciated!
Regards, Maarten
sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= '2015-04-10 00:00:00.0000' ORDER BY completed DESC;
+-----------+ | completed | +===========+ +-----------+ 0 tuples (0.595ms) sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= CAST('2015-04-10 00:00:00.0000' AS TIMESTAMP) ORDER BY completed DESC;
+-----------+ | completed | +===========+ +-----------+ 0 tuples (0.328ms) sql>SELECT completed FROM "reporting"."transactions" ORDER BY completed DESC LIMIT 10; +----------------------------+ | completed | +============================+ | 2015-04-10 11:50:22.051000 | | 2015-04-10 10:55:50.039000 | | 2015-04-09 09:41:13.023000 | | 2015-04-09 09:39:09.079000 | | 2015-04-09 09:35:56.085000 | | 2015-04-08 12:55:13.007000 | | 2015-04-08 12:37:07.002000 | | 2015-04-08 12:28:22.000000 | | 2015-04-08 12:25:25.052000 | | 2015-04-07 21:59:59.099000 | +----------------------------+ 10 tuples (0.683ms)
sql>\d "reporting"."transactions" CREATE TABLE "reporting"."transactions" ( "id" CHAR(36) NOT NULL, "status" VARCHAR(25) NOT NULL, "created" TIMESTAMP NOT NULL, "completed" TIMESTAMP, "currency" CHAR(3) NOT NULL, "amount" BIGINT NOT NULL, "balance" VARCHAR(25) NOT NULL, "payment_method" VARCHAR(50) NOT NULL, "fee_currency" CHAR(3), "fee_amount" BIGINT, "order_id" CHAR(36) NOT NULL, "order_status" VARCHAR(25) NOT NULL, "project_id" CHAR(36) NOT NULL, "merchant_id" CHAR(36) NOT NULL, CONSTRAINT "transactions_id_pkey" PRIMARY KEY ("id") );
Weird thing, it does work when using the 9th:
sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= CAST('2015-04-09 00:00:00.0000' AS TIMESTAMP) ORDER BY completed;
+----------------------------+
| completed |
+============================+
| 2015-04-09 09:35:56.085000 |
| 2015-04-09 09:39:09.079000 |
| 2015-04-09 09:41:13.023000 |
| 2015-04-10 11:50:22.051000 |
+----------------------------+
4 tuples (0.332ms)
sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= CAST('2015-04-10 00:00:00.0000' AS TIMESTAMP) ORDER BY completed;
+-----------+
| completed |
+===========+
+-----------+
0 tuples (0.268ms)
On Fri, Apr 10, 2015 at 2:24 PM, Maarten Sander maarten.sander@gingerpayments.com wrote:
Hi everyone, I’m probably missing something, but it seems that I cannot select entries that are newer than a given timestamp. Below are my attempts and the schema of the table. Any help would be greatly appreciated! Regards, Maarten sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= '2015-04-10 00:00:00.0000' ORDER BY completed DESC; +-----------+ | completed | +===========+ +-----------+ 0 tuples (0.595ms) sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= CAST('2015-04-10 00:00:00.0000' AS TIMESTAMP) ORDER BY completed DESC; +-----------+ | completed | +===========+ +-----------+ 0 tuples (0.328ms) sql>SELECT completed FROM "reporting"."transactions" ORDER BY completed DESC LIMIT 10; +----------------------------+ | completed | +============================+ | 2015-04-10 11:50:22.051000 | | 2015-04-10 10:55:50.039000 | | 2015-04-09 09:41:13.023000 | | 2015-04-09 09:39:09.079000 | | 2015-04-09 09:35:56.085000 | | 2015-04-08 12:55:13.007000 | | 2015-04-08 12:37:07.002000 | | 2015-04-08 12:28:22.000000 | | 2015-04-08 12:25:25.052000 | | 2015-04-07 21:59:59.099000 | +----------------------------+ 10 tuples (0.683ms) sql>\d "reporting"."transactions" CREATE TABLE "reporting"."transactions" ( "id" CHAR(36) NOT NULL, "status" VARCHAR(25) NOT NULL, "created" TIMESTAMP NOT NULL, "completed" TIMESTAMP, "currency" CHAR(3) NOT NULL, "amount" BIGINT NOT NULL, "balance" VARCHAR(25) NOT NULL, "payment_method" VARCHAR(50) NOT NULL, "fee_currency" CHAR(3), "fee_amount" BIGINT, "order_id" CHAR(36) NOT NULL, "order_status" VARCHAR(25) NOT NULL, "project_id" CHAR(36) NOT NULL, "merchant_id" CHAR(36) NOT NULL, CONSTRAINT "transactions_id_pkey" PRIMARY KEY ("id") );
Another update: after adding a new record to the table my original query started working. What’s going on here?
On Fri, Apr 10, 2015 at 2:28 PM, Maarten Sander maarten.sander@gingerpayments.com wrote:
Weird thing, it does work when using the 9th: sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= CAST('2015-04-09 00:00:00.0000' AS TIMESTAMP) ORDER BY completed; +----------------------------+ | completed | +============================+ | 2015-04-09 09:35:56.085000 | | 2015-04-09 09:39:09.079000 | | 2015-04-09 09:41:13.023000 | | 2015-04-10 11:50:22.051000 | +----------------------------+ 4 tuples (0.332ms) sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= CAST('2015-04-10 00:00:00.0000' AS TIMESTAMP) ORDER BY completed; +-----------+ | completed | +===========+ +-----------+ 0 tuples (0.268ms) On Fri, Apr 10, 2015 at 2:24 PM, Maarten Sander maarten.sander@gingerpayments.com wrote:
Hi everyone, I’m probably missing something, but it seems that I cannot select entries that are newer than a given timestamp. Below are my attempts and the schema of the table. Any help would be greatly appreciated! Regards, Maarten sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= '2015-04-10 00:00:00.0000' ORDER BY completed DESC; +-----------+ | completed | +===========+ +-----------+ 0 tuples (0.595ms) sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= CAST('2015-04-10 00:00:00.0000' AS TIMESTAMP) ORDER BY completed DESC; +-----------+ | completed | +===========+ +-----------+ 0 tuples (0.328ms) sql>SELECT completed FROM "reporting"."transactions" ORDER BY completed DESC LIMIT 10; +----------------------------+ | completed | +============================+ | 2015-04-10 11:50:22.051000 | | 2015-04-10 10:55:50.039000 | | 2015-04-09 09:41:13.023000 | | 2015-04-09 09:39:09.079000 | | 2015-04-09 09:35:56.085000 | | 2015-04-08 12:55:13.007000 | | 2015-04-08 12:37:07.002000 | | 2015-04-08 12:28:22.000000 | | 2015-04-08 12:25:25.052000 | | 2015-04-07 21:59:59.099000 | +----------------------------+ 10 tuples (0.683ms) sql>\d "reporting"."transactions" CREATE TABLE "reporting"."transactions" ( "id" CHAR(36) NOT NULL, "status" VARCHAR(25) NOT NULL, "created" TIMESTAMP NOT NULL, "completed" TIMESTAMP, "currency" CHAR(3) NOT NULL, "amount" BIGINT NOT NULL, "balance" VARCHAR(25) NOT NULL, "payment_method" VARCHAR(50) NOT NULL, "fee_currency" CHAR(3), "fee_amount" BIGINT, "order_id" CHAR(36) NOT NULL, "order_status" VARCHAR(25) NOT NULL, "project_id" CHAR(36) NOT NULL, "merchant_id" CHAR(36) NOT NULL, CONSTRAINT "transactions_id_pkey" PRIMARY KEY ("id") );
Hai Maarten,
First I thought 00:00:00.0000 might be interpreted as 24:00:00.000, which explains the empty results (doesn't mean it's correct). But your other query results say otherwise.
Which MonetDB version are you using? Does it happen with newer version of monetdb?
Is the problem with the empty result reproducible? For instance, if you delete the new record, is the result empty again? Or, the problem is reproducible with a fresh database?
Which new record was added? Does it matter what value is in the new record, i.e., what happens if you added a new record with different value?
Would you please give us a small set of DDL statements (i.e., the CREATE TABLE + INSERT INTO statements), with which we can reproduce the problem. In this way, we can further investigate.
Thanks a lot!
Regards,
Jennie
On Apr 10, 2015, at 14:50, Maarten Sander maarten.sander@gingerpayments.com wrote:
Another update: after adding a new record to the table my original query started working. What’s going on here?
On Fri, Apr 10, 2015 at 2:28 PM, Maarten Sander maarten.sander@gingerpayments.com wrote:
Weird thing, it does work when using the 9th:
sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= CAST('2015-04-09 00:00:00.0000' AS TIMESTAMP) ORDER BY completed; +----------------------------+ | completed | +============================+ | 2015-04-09 09:35:56.085000 | | 2015-04-09 09:39:09.079000 | | 2015-04-09 09:41:13.023000 | | 2015-04-10 11:50:22.051000 | +----------------------------+ 4 tuples (0.332ms) sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= CAST('2015-04-10 00:00:00.0000' AS TIMESTAMP) ORDER BY completed; +-----------+ | completed | +===========+ +-----------+ 0 tuples (0.268ms)
On Fri, Apr 10, 2015 at 2:24 PM, Maarten Sander maarten.sander@gingerpayments.com wrote:
Hi everyone,
I’m probably missing something, but it seems that I cannot select entries that are newer than a given timestamp.
Below are my attempts and the schema of the table. Any help would be greatly appreciated!
Regards, Maarten
sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= '2015-04-10 00:00:00.0000' ORDER BY completed DESC; +-----------+ | completed | +===========+ +-----------+ 0 tuples (0.595ms) sql>SELECT completed FROM "reporting"."transactions" WHERE completed >= CAST('2015-04-10 00:00:00.0000' AS TIMESTAMP) ORDER BY completed DESC; +-----------+ | completed | +===========+ +-----------+ 0 tuples (0.328ms) sql>SELECT completed FROM "reporting"."transactions" ORDER BY completed DESC LIMIT 10; +----------------------------+ | completed | +============================+ | 2015-04-10 11:50:22.051000 | | 2015-04-10 10:55:50.039000 | | 2015-04-09 09:41:13.023000 | | 2015-04-09 09:39:09.079000 | | 2015-04-09 09:35:56.085000 | | 2015-04-08 12:55:13.007000 | | 2015-04-08 12:37:07.002000 | | 2015-04-08 12:28:22.000000 | | 2015-04-08 12:25:25.052000 | | 2015-04-07 21:59:59.099000 | +----------------------------+ 10 tuples (0.683ms)
sql>\d "reporting"."transactions" CREATE TABLE "reporting"."transactions" ( "id" CHAR(36) NOT NULL, "status" VARCHAR(25) NOT NULL, "created" TIMESTAMP NOT NULL, "completed" TIMESTAMP, "currency" CHAR(3) NOT NULL, "amount" BIGINT NOT NULL, "balance" VARCHAR(25) NOT NULL, "payment_method" VARCHAR(50) NOT NULL, "fee_currency" CHAR(3), "fee_amount" BIGINT, "order_id" CHAR(36) NOT NULL, "order_status" VARCHAR(25) NOT NULL, "project_id" CHAR(36) NOT NULL, "merchant_id" CHAR(36) NOT NULL, CONSTRAINT "transactions_id_pkey" PRIMARY KEY ("id") );
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi Jennie,
I have no way of reliably reproducing the error. It happens very infrequently and there does not seem to be a pattern. It is always solved by adding another record to the table, no matter the contents of the record. If I remember correctly, deleting the record does not result in the bug appearing again, but I will double check when this issue pops up again.
I am using the Oct2014-SP2 version (11.19.9-20150123) of MonetDB. A newer version is not available in the apt repo (I’m running MonetDB on Ubuntu 14.04.2 LTS).
Regards, Maarten
(Sorry for the delay, I sent this email from the wrong address before and didn't notice the bounce.)
On Tue, Apr 14, 2015 at 12:36 PM, Ying Zhang Y.Zhang@cwi.nl wrote:
Hai Maarten,
First I thought 00:00:00.0000 might be interpreted as 24:00:00.000, which explains the empty results (doesn't mean it's correct). But your other query results say otherwise.
Which MonetDB version are you using? Does it happen with newer version of monetdb?
Is the problem with the empty result reproducible? For instance, if you delete the new record, is the result empty again? Or, the problem is reproducible with a fresh database?
Which new record was added? Does it matter what value is in the new record, i.e., what happens if you added a new record with different value?
Would you please give us a small set of DDL statements (i.e., the CREATE TABLE + INSERT INTO statements), with which we can reproduce the problem. In this way, we can further investigate.
Thanks a lot!
Regards,
Jennie
On Apr 10, 2015, at 14:50, Maarten Sander <
maarten.sander@gingerpayments.com> wrote:
Another update: after adding a new record to the table my original query
started working. What’s going on here?
On Fri, Apr 10, 2015 at 2:28 PM, Maarten Sander <
maarten.sander@gingerpayments.com> wrote:
Weird thing, it does work when using the 9th:
sql>SELECT completed FROM "reporting"."transactions" WHERE completed >=
CAST('2015-04-09 00:00:00.0000' AS TIMESTAMP) ORDER BY completed;
+----------------------------+ | completed | +============================+ | 2015-04-09 09:35:56.085000 | | 2015-04-09 09:39:09.079000 | | 2015-04-09 09:41:13.023000 | | 2015-04-10 11:50:22.051000 | +----------------------------+ 4 tuples (0.332ms) sql>SELECT completed FROM "reporting"."transactions" WHERE completed >=
CAST('2015-04-10 00:00:00.0000' AS TIMESTAMP) ORDER BY completed;
+-----------+ | completed | +===========+ +-----------+ 0 tuples (0.268ms)
On Fri, Apr 10, 2015 at 2:24 PM, Maarten Sander <
maarten.sander@gingerpayments.com> wrote:
Hi everyone,
I’m probably missing something, but it seems that I cannot select
entries that are newer than a given timestamp.
Below are my attempts and the schema of the table. Any help would be
greatly appreciated!
Regards, Maarten
sql>SELECT completed FROM "reporting"."transactions" WHERE completed >=
'2015-04-10 00:00:00.0000' ORDER BY completed DESC;
+-----------+ | completed | +===========+ +-----------+ 0 tuples (0.595ms) sql>SELECT completed FROM "reporting"."transactions" WHERE completed >=
CAST('2015-04-10 00:00:00.0000' AS TIMESTAMP) ORDER BY completed DESC;
+-----------+ | completed | +===========+ +-----------+ 0 tuples (0.328ms) sql>SELECT completed FROM "reporting"."transactions" ORDER BY completed
DESC LIMIT 10;
+----------------------------+ | completed | +============================+ | 2015-04-10 11:50:22.051000 | | 2015-04-10 10:55:50.039000 | | 2015-04-09 09:41:13.023000 | | 2015-04-09 09:39:09.079000 | | 2015-04-09 09:35:56.085000 | | 2015-04-08 12:55:13.007000 | | 2015-04-08 12:37:07.002000 | | 2015-04-08 12:28:22.000000 | | 2015-04-08 12:25:25.052000 | | 2015-04-07 21:59:59.099000 | +----------------------------+ 10 tuples (0.683ms)
sql>\d "reporting"."transactions" CREATE TABLE "reporting"."transactions" ( "id" CHAR(36) NOT NULL, "status" VARCHAR(25) NOT NULL, "created" TIMESTAMP NOT NULL, "completed" TIMESTAMP, "currency" CHAR(3) NOT NULL, "amount" BIGINT NOT NULL, "balance" VARCHAR(25) NOT NULL, "payment_method" VARCHAR(50) NOT NULL, "fee_currency" CHAR(3), "fee_amount" BIGINT, "order_id" CHAR(36) NOT NULL, "order_status" VARCHAR(25) NOT NULL, "project_id" CHAR(36) NOT NULL, "merchant_id" CHAR(36) NOT NULL, CONSTRAINT "transactions_id_pkey" PRIMARY KEY ("id") );
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 (2)
-
Maarten Sander -
Ying Zhang