Postgres Pro
Downloads
Home   >   mailing lists

Thread: Date Format 9999-12-31-00.00.00.000000

Date Format 9999-12-31-00.00.00.000000

From
Dirk Krautschick
Date:
Hi,

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?

Thanks

Dirk

Re: Date Format 9999-12-31-00.00.00.000000

From
Adrian Klaver
Date:
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

Re: Date Format 9999-12-31-00.00.00.000000

From
Kyotaro Horiguchi
Date:
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

Re: Date Format 9999-12-31-00.00.00.000000

From
Laurenz Albe
Date:
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