By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,780 Members | 1,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,780 IT Pros & Developers. It's quick & easy.

timeofday() and CAST

P: n/a
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.: +55 48 281 7158
ICQ 11866179
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 11 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
"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

Nov 11 '05 #2

P: n/a
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

Nov 11 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.