Re: Date Format 9999-12-31-00.00.00.000000

At Thu, 15 Oct 2020 17:59:39 -0700, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote in
> On 10/15/20 1:58 PM, Dirk Krautschick wrote:
> > Hi,
> > because of a migration from DB2 we have a lot of timestamps like
> > 9999-12-31-00.00.00.000000
>
> I'm assuming these got stored in a varchar field?

It seems like an (old-style?) ISO string representation of a date-time
, and seems like DB2's default output format of a timestamp.

The default parser of PostgreSQL doesn't handle that format. That
doesn't identify '-' as a separtor between date and time nor a dot as
a time field separator.

> > What would be the best way to handle this in Postgres also related
> > to overhead and performance (index usage?).
> > Or is
> > TO_TIMESTAMP('9999-12-31-00.00.00.000000', 'YYYY-MM-DD-HH24.MI.SS.US')
> > the only way? And isn't it possible to define this like NLS parameters
> > in Oracle
> > system wide?

I'm not sure what you're going to do exactly, but timestamp is
generally more efficient than string in regard to both space and speed
and I think that the above conversion is the only way of that
conversion. PostgreSQL doesn't have a feature like the NLS parameters.

If the database won't handle timestamps in that format in the future,
converting that column into timestamptz (perhaps it is currently in
text or varchar) works.

ALTER TABLE <tbl> ALTER COLUMN <column> TYPE timestamp USING TO_TIMESTMAP(<column>, 'format');

Otherwise that format is needed to be translated on-the-fly. I'm not
sure which is efficient in that case.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center