1

I have a table for matches. The table has a column named matchdate, which is a datetime field.

If I have 3 matches on 2011-12-01:

  • 2011-12-01 12:00:00
  • 2011-12-01 13:25:00
  • 2011-12-01 16:00:00

How do I query that? How do I query all matches on 1 single date?

I have looked at date_trunc(), to_char(), etc.
Isn't there some "select * where datetime in date" function?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Fred Fickleberry III
  • 2,439
  • 4
  • 34
  • 50

3 Answers3

5

Cast your timestamp value to date if you want simple syntax. Like this:

SELECT *
FROM   tbl
WHERE  timestamp_col::date = '2011-12-01';  -- date literal

However, with big tables this will be faster:

SELECT *
FROM   tbl
WHERE  timestamp_col >= '2011-12-01 0:0'    -- timestamp literal
AND    timestamp_col <  '2011-12-02 0:0';

Reason: the second query does not have to transform every single value in the table and can utilize a simple index on the timestamp column. The expression is sargable.

Note excluded the upper bound (< instead of <=) for a correct selection.
You can make up for that by creating an index on an expression like this:

CREATE INDEX tbl_ts_date_idx ON tbl (cast(timestamp_col AS date));

Then the first version of the query will be as fast as it gets.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    You can get Rails to produce the half-open `[2011-12-01, 2011-12-02)` version with `.where(:date => '2011-12-01' ... '2011-12-02')`, a 2-dot range would use `[2011-12-01, 2011-12-02]` and BETWEEN. Would PostgreSQL also cast everything to dates when using `>=` and `<` or would it be smart enough to know that casting the literals to timestamps would have the same effect and be less work? – mu is too short Jan 12 '12 at 18:47
  • @muistooshort: Postgres is smart enough in that case. That would result in my second query. – Erwin Brandstetter Jan 12 '12 at 22:06
1

not sure if i am missing something obvious here, but i think you can just

select * from table where date_trunc('day', ts) = '2011-12-01';
armin walland
  • 89
  • 5
0

Just use the SQL BETWEEN function like so:

SELECT * FROM table WHERE date BETWEEN '2011-12-01' AND '2011-12-02'

You may need to include times in the date literals, but this should include the lover limit and exclude the upper.

From rails I believe you can do:

.where(:between => '2011-12-01'..'2011-12-02')
Eden Townsend
  • 395
  • 1
  • 6
  • You'd want `.where(:date => '2011-12-01' ... '2011-12-02')`: `:date` rather than `:between` and a triple-dot range to work with the half-open `[2011-12-01, 2011-12-02)` interval instead of a fully closed `[2011-12-01, 2011-12-02]` interval. The `..` does end up as a BETWEEN in the SQL, the `...` ends up using a `>=`/`<` pair. – mu is too short Jan 12 '12 at 18:38