Re: Problem selecting entries newer than a certain date

10 Apr 2015


      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")
);