Ingres, oh ingres!

Another posting as to the insanity that is ingres.

Check out these sql statements:

select date('now');

----------------------
2005-02-23 11:45:00.0


select date('today');

----------------------
2005-02-22 00:00:00.0


select date_trunc('day', date('now'));

----------------------
2005-02-22 00:00:00.0

and more importantly:

select *
  from receipt
 where receipt_date between date('today')
                        and date('today') + date('1 day');

---------------------
128 rows affected

Did the above statement retrieve the correct rows?

No.

When dates are stored in Ingres they are stored in Universal time (GMT), this allows us to share a database across time zones and still sort on correct order. The client side code converts all incoming and out going dates to GMT, by adding/subtracting the appropriate amout, however, internally it fails to do this.

The above query in South Australia returns all rows from 22^nd Feb 2005 09:30:00 to 23^rd Feb 2005 09:30:00, as we are nine and a half hours in front of GMT. To make it work as required we use:

select *
  from receipt
 where receipt_date between date('today') + date('14 hour') + date('30 minute')
                        and date('today') + date('1 day') + date('14 hour') + date('30 minute');

---------------------
149 rows affected

PROVIDED you run this query before 9:30 in the morning. After 9:30am it needs to read:

select *
  from receipt
 where receipt_date between (date('today') - date('1 day')) + date('14 hour') + date('30 minute')
                        and (date('today') - date('1 day')) + date('1 day') + date('14 hour') + date('30 minute');

---------------------
149 rows affected

The 14:30 addition counters the GMT storage, as the dates are stored as GMT, so a transaction at midnight will be stamped 14:30 the previous day.

Now, you could use the system date to figure out which query to use, but you can't. You could always query the datebase to find out the time, but that's affected the same way, and you cannot retrieve that into a local variable.