pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedto_date has beaten me...
5+ messages / 3 participants
[nested] [flat]
* to_date has beaten me...
@ 2003-02-06 04:02 Rodger Donaldson <rodgerd@diaspora.gen.nz>
2003-02-06 04:22 ` Re: to_date has beaten me... Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Rodger Donaldson @ 2003-02-06 04:02 UTC (permalink / raw)
To: pgsql-sql
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"
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: to_date has beaten me...
2003-02-06 04:02 to_date has beaten me... Rodger Donaldson <rodgerd@diaspora.gen.nz>
@ 2003-02-06 04:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-06 04:25 ` Re: to_date has beaten me... Rodger Donaldson <rodgerd@diaspora.gen.nz>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2003-02-06 04:22 UTC (permalink / raw)
To: rodgerd@diaspora.gen.nz; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: to_date has beaten me...
2003-02-06 04:02 to_date has beaten me... Rodger Donaldson <rodgerd@diaspora.gen.nz>
2003-02-06 04:22 ` Re: to_date has beaten me... Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-06 04:25 ` Rodger Donaldson <rodgerd@diaspora.gen.nz>
2003-02-06 05:06 ` Re: to_date has beaten me... Roberto Mello <rmello@cc.usu.edu>
0 siblings, 1 reply; 5+ messages in thread
From: Rodger Donaldson @ 2003-02-06 04:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: to_date has beaten me...
2003-02-06 04:02 to_date has beaten me... Rodger Donaldson <rodgerd@diaspora.gen.nz>
2003-02-06 04:22 ` Re: to_date has beaten me... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-06 04:25 ` Re: to_date has beaten me... Rodger Donaldson <rodgerd@diaspora.gen.nz>
@ 2003-02-06 05:06 ` Roberto Mello <rmello@cc.usu.edu>
2003-02-06 09:19 ` Re: to_date has beaten me... Rodger Donaldson <rodgerd@diaspora.gen.nz>
0 siblings, 1 reply; 5+ messages in thread
From: Roberto Mello @ 2003-02-06 05:06 UTC (permalink / raw)
To: Rodger Donaldson <rodgerd@diaspora.gen.nz>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: to_date has beaten me...
2003-02-06 04:02 to_date has beaten me... Rodger Donaldson <rodgerd@diaspora.gen.nz>
2003-02-06 04:22 ` Re: to_date has beaten me... Tom Lane <tgl@sss.pgh.pa.us>
2003-02-06 04:25 ` Re: to_date has beaten me... Rodger Donaldson <rodgerd@diaspora.gen.nz>
2003-02-06 05:06 ` Re: to_date has beaten me... Roberto Mello <rmello@cc.usu.edu>
@ 2003-02-06 09:19 ` Rodger Donaldson <rodgerd@diaspora.gen.nz>
0 siblings, 0 replies; 5+ messages in thread
From: Rodger Donaldson @ 2003-02-06 09:19 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-02-06 09:19 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-06 04:02 to_date has beaten me... Rodger Donaldson <rodgerd@diaspora.gen.nz>
2003-02-06 04:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-06 04:25 ` Rodger Donaldson <rodgerd@diaspora.gen.nz>
2003-02-06 05:06 ` Roberto Mello <rmello@cc.usu.edu>
2003-02-06 09:19 ` Rodger Donaldson <rodgerd@diaspora.gen.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox