Thread: Adding to a date/time?

I'm having some trouble understanding the behaviour of one of my queries
on version 7.2. I'm sure it's because I don't understand date/times ...
Here is the query I don't understand and it's results
:
$ psql TMP -c "select id, req_del_date3 from invoices order by id"
  id |     req_del_date3
----+------------------------
   1 | 2002-09-18 00:00:00+09
  10 | 2002-09-18 00:00:00+09
  13 | 2002-09-18 00:00:00+09
  17 | 2002-09-18 00:00:00+09

$ psql TMP -c "update invoices set req_del_date3='today+8' where id > 10"
UPDATE 2
$ psql TMP -c "select id, req_del_date3 from invoices order by id"
  id |     req_del_date3
----+------------------------
   1 | 2002-09-18 00:00:00+09
  10 | 2002-09-18 00:00:00+09
  13 | 2002-09-18 01:00:00+09
  17 | 2002-09-18 01:00:00+09

Why does 'today+8' add 1 hour to the timestamp? I could have
understanding adding 8 secs, 8 hours, 8 days, or 8 of anything but it
added 1 hour? What did I miss?

I also tried this with the same results (I assumed 8d would force adding
8 *days* but no ...):

$ psql TMP -c "update invoices set req_del_date3='today+8d' where id > 10"

What does postgresQL think 8d means?

And finally what is the proper SQL to add 8days to a timestamp?

Jc

Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> $ psql TMP -c "update invoices set req_del_date3='today+8' where id > 10"

You can't do arithmetic inside a timestamp literal.  The above actually
gets interpreted as "today" (meaning midnight) in timezone "+8", ie,
midnight 8 hours east of GMT.

            regards, tom lane