PostgreSQL how to get all records for a single date?

  1. 3 months ago

    Tim S

    May 23 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

    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

    sql_date_time timestamp without time zone,

    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.

  2. Daniel T

    May 23 Pre-Release Testers, Xojo Pro Answer

    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.

  3. Tim S

    May 23 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

    Thank you!
    Tim

  4. Jürg O

    May 23 Pre-Release Testers, Xojo Pro
    Edited 3 months ago

    @Tim S WHERE sql_date_time = '2019-05-23%'

    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 % :)
    If that's not working with PostgreSQL, then @Daniel T 's suggestion will do the trick.

  5. Jay M

    May 23 Pre-Release Testers, Xojo Pro NC, USA

    @Tim S 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

    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'
  6. Maximilian T

    May 23 Pre-Release Testers, Xojo Pro Europe, Germany, Berlin
    Edited 3 months ago

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

    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 
  7. Jay M

    May 24 Pre-Release Testers, Xojo Pro NC, USA
    Edited 3 months ago

    @MaximilianTyrtania 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

    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

  8. David C

    May 24 Pre-Release Testers, Xojo Pro Derby, ITM

    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.

  9. Tim S

    May 24 Pre-Release Testers, Xojo Pro Phoenix Arizona USA

    Thanks for all of the feedback everyone!

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

    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

    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

or Sign Up to reply!