Thread: Frustration with date/times/epoch in v7.3.
Table:
Column | Type | Modifiers
-------------------+-----------------------+---------------------------------------------------------------------
imported_date | integer | not null default 0
PG v7.2.1 (nice and clean):
select imported_date, "timestamptz"(imported_date) from server_accounts limit 1;
imported_date | timestamptz
---------------+------------------------
1037498593 | 2002-11-16 18:03:13-08
(1 row)
PG v7.3 (nasty and dirty):
select imported_date, "timestamptz"(imported_date) from server_accounts limit 1;
ERROR: Bad timestamp external representation '1027966107'
select imported_date, "timestamptz"( cast(imported_date as timestamp) ) from server_accounts limit 1;
ERROR: Cannot cast type integer to timestamp without time zone
select imported_date, "timestamptz"( cast(imported_date as timestamptz) ) from server_accounts limit 1;
ERROR: Cannot cast type integer to timestamp with time zone
select imported_date, ('Jan 1 1970'::DATE + (imported_date || ' seconds')::INTERVAL)::timestamp from server_accounts
limit1
imported_date | timestamp
---------------+---------------------
1027966107 | 2002-07-29 18:08:27
The last query works, but you must admit it is pretty nasty. Yes, I could create a from_epoch() function that takes
careof this, but should this really be nessecary?
Is there a better way to go about this in v7.3 without changing the column type?
Mike Benoit wrote:
> select imported_date, ('Jan 1 1970'::DATE + (imported_date || '
seconds')::INTERVAL)::timestamp from server_accounts limit 1
> imported_date | timestamp
> ---------------+---------------------
> 1027966107 | 2002-07-29 18:08:27
test=> select imported_date, abstime(imported_date) from server_accounts;
imported_date | abstime
---------------+------------------------
1037498593 | 2002-11-17 04:03:13+02
1027966107 | 2002-07-29 20:08:27+02
--
Peter Gibbs
EmKel Systems
Thanks Peter, abstime() seems to do the trick. However this paragraph
from the docs worries me:
"The types abstime and reltime are lower precision types which are used
internally. You are discouraged from using any of these types in new
applications and are encouraged to move any old ones over when
appropriate. Any or all of these internal types might disappear in a
future release."
So does this mean that the abstime() functions may dissappear in the
future? I'd rather not have to go through and change all my queries yet
again, is there a function that has a better chance of being around in
the near future that I could use instead?
On Thu, 2002-12-12 at 09:59, Peter Gibbs wrote:
> Mike Benoit wrote:
>
> > select imported_date, ('Jan 1 1970'::DATE + (imported_date || '
> seconds')::INTERVAL)::timestamp from server_accounts limit 1
> > imported_date | timestamp
> > ---------------+---------------------
> > 1027966107 | 2002-07-29 18:08:27
>
> test=> select imported_date, abstime(imported_date) from server_accounts;
> imported_date | abstime
> ---------------+------------------------
> 1037498593 | 2002-11-17 04:03:13+02
> 1027966107 | 2002-07-29 20:08:27+02
>
> --
> Peter Gibbs
> EmKel Systems
--
Best Regards,
Mike Benoit
NetNation Communication Inc.
Systems Engineer
Tel: 604-684-6892 or 888-983-6600
---------------------------------------
Disclaimer: Opinions expressed here are my own and not
necessarily those of my employer
Why not simply use '99991231'::date ?
Mike Benoit wrote:
>
> Thanks Peter, abstime() seems to do the trick. However this paragraph
> from the docs worries me:
>
> "The types abstime and reltime are lower precision types which are used
> internally. You are discouraged from using any of these types in new
> applications and are encouraged to move any old ones over when
> appropriate. Any or all of these internal types might disappear in a
> future release."
>
> So does this mean that the abstime() functions may dissappear in the
> future? I'd rather not have to go through and change all my queries yet
> again, is there a function that has a better chance of being around in
> the near future that I could use instead?
>
> On Thu, 2002-12-12 at 09:59, Peter Gibbs wrote:
> > Mike Benoit wrote:
> >
> > > select imported_date, ('Jan 1 1970'::DATE + (imported_date || '
> > seconds')::INTERVAL)::timestamp from server_accounts limit 1
> > > imported_date | timestamp
> > > ---------------+---------------------
> > > 1027966107 | 2002-07-29 18:08:27
> >
> > test=> select imported_date, abstime(imported_date) from server_accounts;
> > imported_date | abstime
> > ---------------+------------------------
> > 1037498593 | 2002-11-17 04:03:13+02
> > 1027966107 | 2002-07-29 20:08:27+02
> >
> > --
> > Peter Gibbs
> > EmKel Systems
> --
> Best Regards,
>
> Mike Benoit
> NetNation Communication Inc.
> Systems Engineer
> Tel: 604-684-6892 or 888-983-6600
> ---------------------------------------
>
> Disclaimer: Opinions expressed here are my own and not
> necessarily those of my employer
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Thu, 2002-12-12 at 12:23, Jean-Luc Lachance wrote:
> Why not simply use '99991231'::date ?
>
select imported_date, imported_date::date from server_accounts;
ERROR: Cannot cast type integer to date
select imported_date, 'imported_date'::date from server_accounts;
ERROR: Bad date external representation 'imported_date'
Thats why. ;)
> Mike Benoit wrote:
> >
> > Thanks Peter, abstime() seems to do the trick. However this paragraph
> > from the docs worries me:
> >
> > "The types abstime and reltime are lower precision types which are used
> > internally. You are discouraged from using any of these types in new
> > applications and are encouraged to move any old ones over when
> > appropriate. Any or all of these internal types might disappear in a
> > future release."
> >
> > So does this mean that the abstime() functions may dissappear in the
> > future? I'd rather not have to go through and change all my queries yet
> > again, is there a function that has a better chance of being around in
> > the near future that I could use instead?
> >
> > On Thu, 2002-12-12 at 09:59, Peter Gibbs wrote:
> > > Mike Benoit wrote:
> > >
> > > > select imported_date, ('Jan 1 1970'::DATE + (imported_date || '
> > > seconds')::INTERVAL)::timestamp from server_accounts limit 1
> > > > imported_date | timestamp
> > > > ---------------+---------------------
> > > > 1027966107 | 2002-07-29 18:08:27
> > >
> > > test=> select imported_date, abstime(imported_date) from server_accounts;
> > > imported_date | abstime
> > > ---------------+------------------------
> > > 1037498593 | 2002-11-17 04:03:13+02
> > > 1027966107 | 2002-07-29 20:08:27+02
> > >
> > > --
> > > Peter Gibbs
> > > EmKel Systems
> > --
> > Best Regards,
> >
> > Mike Benoit
> > NetNation Communication Inc.
> > Systems Engineer
> > Tel: 604-684-6892 or 888-983-6600
> > ---------------------------------------
> >
> > Disclaimer: Opinions expressed here are my own and not
> > necessarily those of my employer
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Best Regards,
Mike Benoit
NetNation Communication Inc.
Systems Engineer
Tel: 604-684-6892 or 888-983-6600
---------------------------------------
Disclaimer: Opinions expressed here are my own and not
necessarily those of my employer
On 12 Dec 2002, Mike Benoit wrote: > Thanks Peter, abstime() seems to do the trick. However this paragraph > from the docs worries me: > > "The types abstime and reltime are lower precision types which are used > internally. You are discouraged from using any of these types in new > applications and are encouraged to move any old ones over when > appropriate. Any or all of these internal types might disappear in a > future release." > > So does this mean that the abstime() functions may dissappear in the > future? I'd rather not have to go through and change all my queries yet > again, is there a function that has a better chance of being around in > the near future that I could use instead? Actually, you were already using the existance of abstime in 7.2. The base issue that I believe caused the change is that int4->abstime used to be able to be used as an implicit cast and then the abstime could be used as an argument to timestamp(abstime) but now the cast is only allowed if used explicitly. You're probably best off wrapping it in a function "timestamp"(int) for now (probably just 'select "timestamp"($1::abstime);') as that way you're somewhat isolated from changes as long as there's a way to do the conversion (and it's possible that timestamp(int) will do what you want in the future again, since I'm not sure this was an intended consequence).
Fair enough, I did try:
nsd=# select '99990101'::date;
date
------------
9999-01-01
nsd=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
Mike Benoit wrote:
>
> On Thu, 2002-12-12 at 12:23, Jean-Luc Lachance wrote:
> > Why not simply use '99991231'::date ?
> >
>
> select imported_date, imported_date::date from server_accounts;
> ERROR: Cannot cast type integer to date
> select imported_date, 'imported_date'::date from server_accounts;
> ERROR: Bad date external representation 'imported_date'
>
> Thats why. ;)
>
> > Mike Benoit wrote:
> > >
> > > Thanks Peter, abstime() seems to do the trick. However this paragraph
> > > from the docs worries me:
> > >
> > > "The types abstime and reltime are lower precision types which are used
> > > internally. You are discouraged from using any of these types in new
> > > applications and are encouraged to move any old ones over when
> > > appropriate. Any or all of these internal types might disappear in a
> > > future release."
> > >
> > > So does this mean that the abstime() functions may dissappear in the
> > > future? I'd rather not have to go through and change all my queries yet
> > > again, is there a function that has a better chance of being around in
> > > the near future that I could use instead?
> > >
> > > On Thu, 2002-12-12 at 09:59, Peter Gibbs wrote:
> > > > Mike Benoit wrote:
> > > >
> > > > > select imported_date, ('Jan 1 1970'::DATE + (imported_date || '
> > > > seconds')::INTERVAL)::timestamp from server_accounts limit 1
> > > > > imported_date | timestamp
> > > > > ---------------+---------------------
> > > > > 1027966107 | 2002-07-29 18:08:27
> > > >
> > > > test=> select imported_date, abstime(imported_date) from server_accounts;
> > > > imported_date | abstime
> > > > ---------------+------------------------
> > > > 1037498593 | 2002-11-17 04:03:13+02
> > > > 1027966107 | 2002-07-29 20:08:27+02
> > > >
> > > > --
> > > > Peter Gibbs
> > > > EmKel Systems
> > > --
> > > Best Regards,
> > >
> > > Mike Benoit
> > > NetNation Communication Inc.
> > > Systems Engineer
> > > Tel: 604-684-6892 or 888-983-6600
> > > ---------------------------------------
> > >
> > > Disclaimer: Opinions expressed here are my own and not
> > > necessarily those of my employer
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 2: you can get off all lists at once with the unregister command
> > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> --
> Best Regards,
>
> Mike Benoit
> NetNation Communication Inc.
> Systems Engineer
> Tel: 604-684-6892 or 888-983-6600
> ---------------------------------------
>
> Disclaimer: Opinions expressed here are my own and not
> necessarily those of my employer