Postgres Pro
Downloads
Home   >   mailing lists

Thread: to_date has beaten me...

to_date has beaten me...

From
Rodger Donaldson
Date:
Postgresql 7.2.3

insert    
into     log_entries 
values    (to_date('06/Feb/2003:11:29:11 +13', 'DD/Mon/YYYY:HH24:MI:SS'),0,302,[... various other data elided ...]);

Always inserts the correct date, but sets the time to midnight.

Thinking this might be solved by shuffling with the source data, I've
tried such combinations as:

to_date('06/Feb/2003:11:29:11', 'DD/Mon/YYYY:HH24:MI:SS')
to_date('06/Feb/2003:11:29:11 +13', 'DD/Mon/YYYY:HH:MI:SS')
to_date('06/02/2003:11:29:11', 'DD/MM/YYYY:HH24:MI:SS')

...and so forth, but despite the column in question being a timestamp
with timszone, everything except the date gets truncated.

-- 
Rodger Donaldson        rodgerd@diaspora.gen.nz
"My ATEX terminal isn't working"
"Is there power to the keyboard?"
"No, and it has smoke and flames coming out of it"

Re: to_date has beaten me...

From
Tom Lane
Date:
Rodger Donaldson <rodgerd@diaspora.gen.nz> writes:
> values    (to_date('06/Feb/2003:11:29:11 +13', 'DD/Mon/YYYY:HH24:MI:SS'),0,302,

> Always inserts the correct date, but sets the time to midnight.

Um, well, yeah ... it's to_DATE, meaning it produces a result of type
date.  I think you wanted to_timestamp().
        regards, tom lane

Re: to_date has beaten me...

From
Rodger Donaldson
Date:
On Wed, Feb 05, 2003 at 11:22:57PM -0500, Tom Lane wrote:
> Rodger Donaldson <rodgerd@diaspora.gen.nz> writes:
> > values    (to_date('06/Feb/2003:11:29:11 +13', 'DD/Mon/YYYY:HH24:MI:SS'),0,302,
> 
> > Always inserts the correct date, but sets the time to midnight.
> 
> Um, well, yeah ... it's to_DATE, meaning it produces a result of type
> date.  I think you wanted to_timestamp().

Why yes.  Yes I did.  Too much time spent with Oracle, evidently.
Next I'll be wondering why DECODE doesn't work...

-- 
Rodger Donaldson        rodgerd@diaspora.gen.nz
"How do I set my laser printer for stun?"-- William Tansil

Re: to_date has beaten me...

From
Roberto Mello
Date:
On Thu, Feb 06, 2003 at 05:25:39PM +1300, Rodger Donaldson wrote:
> On Wed, Feb 05, 2003 at 11:22:57PM -0500, Tom Lane wrote:
> > Rodger Donaldson <rodgerd@diaspora.gen.nz> writes:
> > > values    (to_date('06/Feb/2003:11:29:11 +13', 'DD/Mon/YYYY:HH24:MI:SS'),0,302,
> > 
> > > Always inserts the correct date, but sets the time to midnight.
> > 
> > Um, well, yeah ... it's to_DATE, meaning it produces a result of type
> > date.  I think you wanted to_timestamp().
> 
> Why yes.  Yes I did.  Too much time spent with Oracle, evidently.
> Next I'll be wondering why DECODE doesn't work...

Because DECODE is non-standard AFAIK. You can use CASE WHEN in PostgreSQL,
which IIRC, started being supported in Oracle 8.1.7.

-Roberto

-- 
+----|        Roberto Mello   -    http://www.brasileiro.net/  |------+
+       Computer Science Graduate Student, Utah State University      +
+       USU Free Software & GNU/Linux Club - http://fslc.usu.edu/     +
The linuX Files -- The Source is Out There.-- Sent in by Craig S. Bell, goat@aracnet.com

Re: to_date has beaten me...

From
Rodger Donaldson
Date:
On Wed, Feb 05, 2003 at 10:06:35PM -0700, Roberto Mello wrote:
> On Thu, Feb 06, 2003 at 05:25:39PM +1300, Rodger Donaldson wrote:
> > 
> > Why yes.  Yes I did.  Too much time spent with Oracle, evidently.
> > Next I'll be wondering why DECODE doesn't work...
> 
> Because DECODE is non-standard AFAIK. You can use CASE WHEN in PostgreSQL,
> which IIRC, started being supported in Oracle 8.1.7.

It was a joke.  A feeble one, I know.  CASE actually turned up in
8.1.6 - I ended up rewriting a chunk of a client's codebase, mostly
because DECODE is so illegible next to CASE.

-- 
Rodger Donaldson        rodgerd@diaspora.gen.nz
I just had this vision of a young boy cowering in terror, whispering:
"I see dumb people"    -- Steve VanDevender