Postgresql querying timestamp data with date only

Question

I have this data.

this is my query

SELECT transaction_date
FROM 
tenant1.txn_transaction_record  where   '2015-04-14'

The said query renders empty result. Is it possible to query timestamp field using only date?

Answer 1

When you do:

transaction_date = '2015-04-14'

PG will convert string '2015-04-14' to timestamp '2015-04-14 00:00:00' value.

If you do:

transaction_date::date = '2015-04-14'

PG will convert both values to date (wich is only date part, without time part), and it'll work.

BUT... BE CAREFUL WHEN CASTING COLUMNS IN WHERE CLAUSE, because PG will not be able to take advantage of an index that contains that column, unless you've created the index with same cast on the column.

If you create only this index:

create index i_foo_1 on foo ( timestamp_field );

This query WILL NOT use that index:

select * 
from   foo 
where  timestamp_field::date = '2015-04-15';

So, or you'll need to create an aditional index:

create index i_foo_2 on foo ( timestamp_field::date );

Or you'll have to change your original "where clause":

select * 
from   foo 
where  timestamp_field >= ('2015-04-15'::timestamp) 
and    timestamp_field < (('2015-04-15'::date)+1)::timestamp; 

Answer 2

No, If you do like transaction_date = '2015-04-14' It will automatically search for transaction_date = '2015-04-14T00:00:00' So you wont yield any result. Therefore if you want to search the date try transaction_date::date = '2015-04-14'
So the final query is,

SELECT transaction_date
FROM 
tenant1.txn_transaction_record  where  transaction_date::date = '2015-04-14'