Postgres Pro
Downloads
Home   >   mailing lists

Thread: Can LC_TIME affect timestamp input?

Can LC_TIME affect timestamp input?

From
Paul Jones
Date:
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'
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).

I created a special locale with the DB2 timestamp format defined and did

        set lc_time='en_DB.UTF-8';

It didn't affect anything, in or out with Postgres.  I know the locale
works because date(1) displays the DB2 format correctly.

Postgres version:       9.2.2 (Built from source)
OS:                     Centos 6.3

Paul Jones


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Can LC_TIME affect timestamp input?

From
Jasen Betts
Date:
On 2013-01-25, Paul Jones <pbj@cmicdo.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

> 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?

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

what's "DB" ?

> 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.
.



--
⚂⚃ 100% natural

Re: Can LC_TIME affect timestamp input?

From
Paul Jones
Date:



----- Original Message -----
> From: Jasen Betts <jasen@xnet.co.nz>
> To: pgsql-general@postgresql.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@cmicdo.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: Can LC_TIME affect timestamp input?

From
Jasen Betts
Date:
On 2013-01-28, Paul Jones <pbj@cmicdo.com> wrote:

>
> 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.

Strftime doesn't understand fractions of seconds, which makes it
unsuitable for postgres, also certian modern platforms have only
a primitive strftime, which is likely to cause headaches for developers.

Perhaps you could create a new type based on timestamp and write an input
function that fixes the DB2 output, you'd then be stuck with your new
type instead of the esisting one but it shouldn't be hard to make it
compatible.  (unless you want to attack the system tables to change it
in a non-standard way)


--
⚂⚃ 100% natural