Date Format 9999-12-31-00.00.00.000000

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?

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?

Thanks

Dirk

--
Adrian Klaver
adrian.klaver@aklaver.com

At Thu, 15 Oct 2020 17:59:39 -0700, Adrian Klaver <adrian.klaver@aklaver.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

On Thu, 2020-10-15 at 20:58 +0000, Dirk Krautschick wrote:

because of a migration from DB2 we have a lot of timestamps like

9999-12-31-00.00.00.000000

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 would replace them with 'infinity', which is a valid timestamp value
in PostgreSQL.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com