Forum OpenACS Q&A: to_date vs. to_timestamp

Collapse
1: to_date vs. to_timestamp
Posted by Lars Pind on 07/18/03 03:39 AM
Yon and I were just debugging a PG bug where we were trying to set a timestamptz column by saying something like

... to_date('2003-07-18 04:00 PM', 'YYYY-MM-DD HH24:MI AM') ...

but the time didn't get set.

Then we realized that in PG, you have to say to_timestamp, not to_date, if you actually care about the time.

As opposed to Oracle, which uses to_date for both.

And we figured there are probably hundreds of bugs related to this in OpenACS.

So we figured we'd mention it :)

/Lars

Collapse
2: Re: to_date vs. to_timestamp (response to 1)
Posted by Jarkko Laine on 07/18/03 09:41 AM
Now I understand why you guys seem to have been online 24h last couple of days :)

I've also in a middle of a porting effort, so here comes a few more issues which might or might not be more obvious than the one Lars posted.

  • coalesce() in PG is not the same as decode() in Oracle. This should be obvious if one had read their docs, but still I found some code where they had been treated like they were 1-to-1. coalesce(x,y,z) selects first of its arguments that is not NULL and therefore corresponds (mostly) to nvl(x,y) in Oracle. nvl can only take two arguments, coalesce as many as you want. In contrast, decode corresponds to normal (CASE WHEN ... THEN ... ELSE) syntax in PostgreSQL (and SQL92). If you port decode function to a coalesce function, you won't get an error but a possibly really hard-to-find misbehaviour from your program.
  • trunc(datevalue) doesn't work in PG. Instead, you have to use date_trunc and there the accuracy of truncation comes before the source (like date_trunc('date','2003-07-18 04:00 PM')), not after it like in oracle syntax. You also have to state the accuracy, you can't leave it out and get default behaviour like in Oracle (which is day, btw). This is, however, easy to find out since you'll get an error if you do something wrong.
I think I had something else in my mind, too, but I forgot it already :) Maybe I'll get back to this later.
Collapse
3: Re: decode vs case (response to 1)
Posted by Ola Hansson on 07/18/03 11:56 AM
I believe we should encourage/recommend the use of CASE in Oracle too (instead of DECODE), since it increases the chance that one common query file is enough. And also because it is SQL 92 ...

In my experience, at least, it works on 8.1.7. Don't know about older versions.

Collapse
4: Re: decode vs case (response to 3)
Posted by Jarkko Laine on 07/18/03 12:29 PM
I agree with you Ola, though I'm not an experienced Oracle programmer. The purpose of a CASE block is IMHO even easier to understand than decode when you read someone other's code.
Collapse
5: Re: to_date vs. to_timestamp (response to 1)
Posted by Don Baccus on 07/18/03 04:06 PM
Some of this stuff's in our old porting guide, which should still be in file storage ...

Oracle 9i at least implements the SQL standard timestamp types.  Eventually we should be able to merge much of this stuff but that's just a light-at-the-end-of-the-tunnel comment.  Obviously we don't even support 9i at the moment so we're not in any position to force people to use it :)

Oracle 8.1.7 supports one form of CASE, not both of the SQL standard forms.