6

Currently my table has a column to store date and time. The data type of that column is timestamp without time zone. So it has values in the format '2011-09-13 11:03:44.537'.

I need to retrieve rows with respect to the date. If I use:

select * from table where mdate >= '2011-09-13 11:03:44.537'
                      and mdate <= '2011-09-12 11:03:44.537'

it will provide the values which are in between '2011-09-13 11:03:44.537' and '2011-09-12 11:03:44.537'.

But if I am going with:

select * from table where mdate >= '2011-09-13 00:00:00.0'
                      and mdate <= '2011-09-12 00:00:00.0'

without date, month and seconds, It is not displaying any rows.

How to fetch values from this table with respect to the date (only with date, ignoring hour, minutes and seconds)?

Even, the column has date with timestamp, I need to search them only with date (ignoring timestamp or making the hour, minutes and seconds to 0 such as '2011-09-13 00:00:00.0').

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228

2 Answers2

7

If you are going to cast the timestamp values of the field to date, as suggested in another answer, performance will degrade because every single value in the column needs to be cast for comparison and simple indexes cannot be used. You would have to create a special index on the expression, like so:

CREATE INDEX some_idx ON tbl (cast(mdate AS date));

In this case the query should also be simplified to:

SELECT * FROM tbl
WHERE  mdate::date = '2011-09-12'::date; -- 2nd cast optional

However, as far as I can see, your problem is a simple typo / thinko in your query:
You switched upper & lower boundaries. Try:

SELECT * FROM tbl
WHERE  mdate >= '2011-09-12 00:00:00.0'
AND    mdate <= '2011-09-13 00:00:00.0';

Or, to simplify your syntax and be more precise:

SELECT * FROM tbl
WHERE  mdate >= '2011-09-12 00:00'
AND    mdate <  '2011-09-13 00:00';
  • There is no need to spell out seconds and fractions that are 0.
  • You don't want to include 2011-09-13 00:00, so use < instead of <=.
    Don't use BETWEEN here, for the same reason:
WHERE mdate BETWEEN '2011-09-12 00:00' AND '2011-09-13 00:00'

This would include 00:00 of the next day.

Also be aware that a column of type timestamp [without time zone] is interpreted according to your current time zone setting. The date part depends on that setting, which should generally work as expected. More details:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

Just treat the timestamp as a date:

select * 
from table 
where mdate::date >= DATE '2011-09-12' 
  and mdate::date <= DATE '2011-09-13'

The expression mdate::date will cast the timestamp to a date type which will remove the time part from the value.

DATE '2011-09-13' is a (standard) DATE literal which is a bit more robust than simply writing '2011-09-13' as it isn't affected by any language settings.

  • `mdate::date` and `DATE mdate` ar synonymous. `'2011-09-13'::date` and `date '2011-09-13'` are synonymous. Just different syntax styles. You make it seem as if there was a difference. – Erwin Brandstetter Sep 16 '11 at 01:11
  • @Erwin: doesn't `'2011-09-13'` rely on system settings? So that it could work on one installation but not on another? –  Sep 16 '11 at 06:57
  • That question is orthogonal to my statement concerning the cast syntax. Aside from that, 'yyyy-mm-dd' for a date is unambiguous with any locale. [http://www.postgresql.org/docs/9.0/interactive/datatype-datetime.html](http://www.postgresql.org/docs/9.0/interactive/datatype-datetime.html) – Erwin Brandstetter Sep 16 '11 at 11:07
  • @Erwin: thanks for clarifying that (you live and learn...) I thought it was influenced by the `DateStyle` configuration setting: http://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-DATESTYLE –  Sep 16 '11 at 11:53
  • @a_horse_with_no_name: some details in the input format of date/time data *are* influenced by the `DateStyle` setting, you were not far off there. But not the cast syntax, and not the ISO 8601 format 'yyyy-mm-dd' which is [unambiguous in any mode](http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-DATETIME-INPUT). – Erwin Brandstetter Sep 18 '11 at 17:15