Date Format 9999-12-31-00.00.00.000000

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Date Format 9999-12-31-00.00.00.000000

Dirk Krautschick
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
Reply | Threaded
Open this post in threaded view
|

Re: Date Format 9999-12-31-00.00.00.000000

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Date Format 9999-12-31-00.00.00.000000

Kyotaro Horiguchi-4
At Thu, 15 Oct 2020 17:59:39 -0700, Adrian Klaver <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Date Format 9999-12-31-00.00.00.000000

Laurenz Albe
In reply to this post by Dirk Krautschick
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