timeofday() and CAST

[Vilson farias]

Greetings,

I was executing some queries when I discovered that 7.3.4's CAST is a
little different from 7.1.2 when working with timestamps. When working with
localized timestamps, I can't use CAST to convert a string for a timestamp
anymore. Please take a look in the following scripts.

#### PostgreSQL 7.1.2 ####

bxs=# SELECT timeofday();
timeofday
-------------------------------------
Thu Aug 21 09:58:57.975598 2003 BRT
(1 row)

bxs=# SELECT CAST(timeofday() AS timestamp);
?column?
---------------------------
2003-08-21 09:59:22.16-03
(1 row)
#### PostgreSQL 7.3.4 ####

bxs=# SELECT timeofday();
timeofday
-------------------------------------
Thu Aug 21 10:04:18.215420 2003 BRT
(1 row)

bxs=# SELECT CAST(timeofday() AS timestamp);
ERROR: Bad timestamp external representation 'Thu Aug 21 10:04:42.597819
2003 BRT'
bxs=#

bxs=# SELECT CAST('Thu Aug 21 10:04:27.203170 2003 BRT' AS timestamp);
ERROR: Bad timestamp external representation 'Thu Aug 21 10:04:27.203170
2003 BRT'

bxs=# SELECT CAST('Thu Aug 21 10:04:27.203170 2003' AS timestamp);
timestamp
---------------------------
2003-08-21 10:04:27.20317
(1 row)

Best regards

----------------------------------------------------------------------------
----
José Vilson de Mello de Farias
Software Engineer

Dígitro Tecnologia Ltda - www.digitro.com.br
APC - Customer Oriented Applications
E-mail: vi***********@digitro.com.br
Tel.: [phone]
ICQ 11866179
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

"Vilson farias" <vi***********@digitro.com.br> writes:

#### PostgreSQL 7.3.4 #### bxs=# SELECT timeofday();
timeofday
-------------------------------------
Thu Aug 21 10:04:18.215420 2003 BRT
(1 row) bxs=# SELECT CAST(timeofday() AS timestamp);
ERROR: Bad timestamp external representation 'Thu Aug 21 10:04:42.597819
2003 BRT'
bxs=#

It's unhappy about "BRT", which is not a known timezone name. Not sure
why the earlier version didn't complain too.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Alvaro Herrera]

On Thu, Aug 21, 2003 at 02:58:40PM -0300, Vilson farias wrote:

I've been taking a look at documentation about timezones (Appendix A/Time
Zone Abbreviations) and it seems that BRT is really missing in that list.
Isn't Brazil important/big enough to have at least BRT and BRST timezones
supported from PostgreSQL? IMHO maybe it's time for a more robust support
for timezones, because maybe more people from other countries are getting
the same problem in newer PostgreSQL releases, since timestamps from strings
are not so flexible as they were in older releases.

Hmm... I'm seeing Chile standard timezones, so it's not a matter of how
big the country is. However, in the table at
src/backend/utils/adt/datetime.c I'm seeing conflict between BST
(British Summer Time), BST (Brazil Standard Time) and BST (Bering
Standard Time), so they just picked one. However there's a timezone
called "BRA" that's apparently "Brazil Time".

I agree however that it's a pretty ugly bug that you can get a timezone
that the server won't accept back.

Maybe the bug is that src/backend/utils/adt/nabstime.c::timeofday() uses
%Z instead of %z.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Thou shalt not follow the NULL pointer, for chaos and madness await
thee at its end." (2nd Commandment for C programmers)

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings