Re: Problem selecting entries newer than a certain date

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 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 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 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