Re: Can LC_TIME affect timestamp input?

----- Original Message -----
> From: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
> To: pgsql-general(at)postgresql(dot)org
> Cc:
> Sent: Saturday, January 26, 2013 9:00 PM
> Subject: Re: [GENERAL] Can LC_TIME affect timestamp input?
>
> On 2013-01-25, Paul Jones <pbj(at)cmicdo(dot)com> wrote:
>> Is it possible for LC_TIME locale to affect the format with which
>> timestamps are input?
>
>> I have DB2 CSV dumps with timestamps like
> '2003-10-21-22.59.44.000000'
>
> All the non-digit symbols between "21" and the "44" look
> unusual

Yes, they do, but that's what came out of DB2 (it is what it is...)

>
>> that I want to load into Postgres with \copy.  I would like to
> eliminate
>> the sed scripts that convert the timestamps in order to speed up the load.
>
>> (I know I could stage it through a temp table and use to_timestamp()
>> but that requires a temp table for each real table, which is not viable
>> w.r.t. our project goals).
>
> could you use a text column and then do alter table?

The project sponsors won't accept this (but this is a good addition to my personal
toolbox for another day, thanks)

>
>> I created a special locale with the DB2 timestamp format defined and did
>>
>>         set lc_time='en_DB.UTF-8';
>
> what's "DB" ?

Just made it up...stands for DB2.

>
>> It didn't affect anything, in or out with Postgres.  I know the locale
>> works because date(1) displays the DB2 format correctly.
>
> That feels like a bug somewhere
>
>
> AIUI date uses strftime("%c",...
> and strftime() doesn't support fractional seconds, so postgres can't
> use it.

Since posting this, I tried digging around in the source code.  From looking at
"timestamp_in" and related routines, it doesn't appear to take into account
any LC_* environment var.  And I didn't see strftime(3) used for timestamps
(although I could have missed it.)  Timestamp input seems hard-wired for the
most part.

PJ

> .
>
>
>
> --
> ⚂⚃ 100% natural
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>