Search code examples
sqldatabaseapache-phoenix

SQL CASE WHEN Logic does not work for comparison to NOW() date

I am using Apache Phoenix Query Server. I need to manipulate the payment_status column so that it follows the logic below and be corrected.

However, I noticed that even when paid_date is equal to now_date, the converted status (new_payment_status) becomes "Overdue" instead of "Due". Any ideas why was that happening?

SELECT paid_date, TO_DATE(TO_CHAR(paid_date)), NOW() AS now_date, payment_status AS old_payment_status, 
(CASE WHEN (TO_DATE(TO_CHAR(paid_date)) = NOW()) THEN 'Due'
    WHEN (TO_DATE(TO_CHAR(paid_date)) < NOW()) THEN 'Overdue'
    WHEN (TO_DATE(TO_CHAR(paid_date)) > NOW()) THEN 'Paid' 
ELSE 'No Status'
END) as new_payment_status
FROM utilities_table WHERE bill='123';

Result and Metadata:
Edit: Embeded a screenshot instead, because markdown for tables aren't translating properly.
Result and Metadata Tables

Since I'm really not that familiar with Phoenix Query Server, I tried to read how does TO_DATE and NOW() works.

Found that there are also other parameters for TO_DATE, and thought it could be because of the timezone.
https://phoenix.apache.org/language/functions.html#to_date
https://phoenix.apache.org/language/functions.html#now

However, that wouldn't really make sense because as you could see from the result of the query above -- that was the value it retrieved and was trying to compare. I also thought it may be because they have different data types, but as per the metadata, they're both DATEs.

Solution

  • When you convert paid_date to a String, you are changing its format, which does not include the time component.

    Also, date comparison only returns true if both dates are exactly equal.

    Therefore, TO_DATE(TO_CHAR(paid_date)) = NOW() never works.

    Try using TRUNC(paid_date) = TRUNC(NOW()) if the time doesn't matter for you, or paid_date = NOW() if you want to check the time as well."