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?
Add a comment
|
3 Answers
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;
Comments
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'
1 Comment
Christian
Offtopic: Be careful when casting columns in "where" clause. Most times it will not use any index (unless you've created the index with that cast column)
SELECT transaction_date
FROM
tenant1.txn_transaction_record where date_trunc('day', transaction_date) = '2015-04-14 00:00:00'
I don't have a postgres database up to try it :-)
