Search code examples
informix

Informix today operator

I have a condition qa.actual_date >= today - 1 in a informix query. Does it fetch the records exactly from the past 24 hours?

Eg: Curent date and time is 13 Jun 2019 12:45 PM does qa.actual_date >= today - 1 will fetch the records from 12 Jun 2019 12:45 PM or 12 Jun 2019 12:00 AM

Solution

  • Under Informix, a DATE value refers to a day, and has no explicit time component. Given a current date of 2019-06-13 and assuming the type of qa.actual_date is DATE (and not a DATETIME type), the condition:

    qa.actual_date >= TODAY - 1

    selects all records where the qa.actual_date value is (any time on) 2019-06-12 or later.

    If qa.actual_date is of type DATETIME YEAR TO SECOND or any other type that has an hour, minute, or second component (as well as day, month, year components), then the value of TODAY - 1 will be converted (extended) to that type, and the missing time components will be treated as zeroes.

    SELECT EXTEND(TODAY - 1, YEAR TO SECOND) FROM sysmaster:sysdual;

    That will return 2019-06-12 00:00:00.