PostgreSQL how to get all records for a single date?

Hello all.

I am converting from SQLite to PostgreSQL and have found that working with dates is not working! I have the following code which does not error, but also does not provide the expected results (for which I know there are at least 5 records.

SELECT * FROM transaction_access WHERE sql_date_time = '2019-05-23%' ORDER BY sql_date_time DESC

Even if I try this in the SQL part of pgAmin3 I get no errors, but no records. The field data type is

What am I doing wrong?
Thanks,
Tim

I’ve always done…

SELECT * FROM transaction_access WHERE sql_date_time >= '2019-05-23 00:00:00' and sql_date_time <= '2019-05-23 23:59:59' ORDER BY sql_date_time DESC

That may be an antiquated habit but it works across SQL databases.

Thank you!
Tim

shouldn’t that be: WHERE sql_date_time LIKE '2019-05-23%' ?
I doubt you have saved the values with a %, but your query suggest you want to retrieve only those containing a % :slight_smile:
If that’s not working with PostgreSQL, then @Daniel Taylor 's suggestion will do the trick.

[quote=437950:@Tim Seyfarth]I am converting from SQLite to PostgreSQL and have found that working with dates is not working! I have the following code which does not error, but also does not provide the expected results (for which I know there are at least 5 records.

SELECT * FROM transaction_access WHERE sql_date_time = ‘2019-05-23%’ ORDER BY sql_date_time DESC[/quote]
Are you saying the above works for you in SQLite? I don’t see how - it’s not a valid WHERE statement. As Jürg said, maybe you meant “LIKE” instead of “=”? And if it is supposed to be LIKE, then the reason it works with SQLite is because SQLite is treating the column as TEXT instead of DATE (a data type SQLIte doesn’t actually have). In PostgreSQL, like most other databases, columns are strongly typed, so the query engine can’t just auto-convert the column to make your query work. You have to use valid date/time values to compare against a date/time column. You’ll need to keep this in mind with other data types during your conversion process.

Daniel’s suggestion will work, though you don’t really need the time part if you are wanting all values for that day. You can just use

WHERE sql_date_time >= '2019-05-23' and sql_date_time < '2019-05-24'

[quote]WHERE sql_date_time >= ‘2019-05-23’ and sql_date_time < ‘2019-05-24’
[/quote]

Also note that this will surprisingly not return any results if the sql_date_time field is of type timestamp (which given the name it should be). You need to cast to date:

WHERE sql_date_time::date >= '2019-05-23'::date and sql_date_time::date < '2019-05-24'::date

or

WHERE sql_date_time::date between '2019-05-23'::date and '2019-05-24'::date 

So explicitly casting ‘2019-05-24’ to a date is optional - casting a timestamp to a date is mandatory.

BETWEEN is just a shortcut for “>= and <=”. So be aware of wether you want to include 2019-05-24.

Oh, now that I actually look at the dates I realize all you want is

WHERE sql_date_time::date = '2019-05-23'::date 

No, not necessary. Postgres casts the date to a timestamp to compare values. So the string comparison value ‘2019-05-23’ is cast to ‘2019-05-23 00:00:00’. I do it all the time and it works fine. Plus, casting each row timestamp value to date as you show causes more overhead versus casting the comparison value once.

And, yes, a BETWEEN clause used with a date-only comparison value will include any timestamp equal to exactly midnight of the second comparison value, which is why I didn’t use it. E.g., the following would match all timestamps on 2019-05-23 plus the exact timestamp of 2019-05-24 00:00:00

WHERE sql_date_time between '2019-05-23' and '2019-05-24'

Edit: I have ignored microseconds in all of this, which PostgreSQL defaults to zero unless specified. When I said the timestamp of exactly midnight, that really is 00:00:00.0

If you ran this nightly, wouldn’t there be rare occasions when midnight is counted twice?

Since the PostgreSQL BETWEEN clause includes the extreme values (unlike English), surely the BETWEEN ‘… 00:00:00’ AND ‘…11:59:59’ is best.

Thanks for all of the feedback everyone!

I got it to work by including a from and to time component.

Casting is something new to me, so will have to work with that in the future as required!

Thanks again all for the feedback and education!
Tim