Thread: timeofday() and CAST
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: vilson.farias@digitro.com.br
Tel.: +55 48 281 7158
ICQ 11866179
"Vilson farias" <vilson.farias@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
Mr. Lane, 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. Here is a list of Brazilian timezones. # Zone NAME GMTOFF RULES FORMAT [UNTIL] # # Atlantic islands: Fernando de Noronha, Trindade, Martin Vaz, # Atol das Rocas, and Penedos de Sao Pedro e Sao Paulo Zone America/Noronha -2:09:40 - LMT 1914 -2:00 Brazil FN%sT 1990 Sep 17 -2:00 - FNT # # Amapa (AP), east Para (PA) # East Para includes Belem, Maraba, Serra Norte, and Sao Felix do Xingu. Zone America/Belem -3:13:56 - LMT 1914 -3:00 Brazil BR%sT 1988 Sep 12 -3:00 - BRT # # Maranhao (MA), Piaui (PI), Ceara (CE), Rio Grande do Norte (RN), # Paraiba (PB) Zone America/Fortaleza -2:34:00 - LMT 1914 -3:00 Brazil BR%sT 1990 Sep 17 -3:00 - BRT 1999 Sep 30 -3:00 Brazil BR%sT 2000 Oct 22 -3:00 - BRT 2001 Sep 13 -3:00 Brazil BR%sT # # Pernambuco (PE) (except Atlantic islands) Zone America/Recife -2:19:36 - LMT 1914 -3:00 Brazil BR%sT 1990 Sep 17 -3:00 - BRT 1999 Sep 30 -3:00 Brazil BR%sT 2000 Oct 15 -3:00 - BRT 2001 Sep 13 -3:00 Brazil BR%sT # # Tocantins (TO) Zone America/Araguaina -3:12:48 - LMT 1914 -3:00 Brazil BR%sT 1990 Sep 17 -3:00 - BRT 1995 Sep 14 -3:00 Brazil BR%sT # # Alagoas (AL), Sergipe (SE) Zone America/Maceio -2:22:52 - LMT 1914 -3:00 Brazil BR%sT 1990 Sep 17 -3:00 - BRT 1995 Oct 13 -3:00 Brazil BR%sT 1996 Sep 4 -3:00 - BRT 1999 Sep 30 -3:00 Brazil BR%sT 2000 Oct 22 -3:00 - BRT 2001 Sep 13 -3:00 Brazil BR%sT # # Bahia (BA), Goias (GO), Distrito Federal (DF), Minas Gerais (MG), # Espirito Santo (ES), Rio de Janeiro (RJ), Sao Paulo (SP), Parana (PR), # Santa Catarina (SC), Rio Grande do Sul (RS) Zone America/Sao_Paulo -3:06:28 - LMT 1914 -3:00 Brazil BR%sT 1963 Oct 23 00:00 -3:00 1:00 BRST 1964 -3:00 Brazil BR%sT # # Mato Grosso (MT), Mato Grosso do Sul (MS) Zone America/Cuiaba -3:44:20 - LMT 1914 -4:00 Brazil AM%sT # # west Para (PA), Rondonia (RO) # West Para includes Altamira, Oribidos, Prainha, Oriximina, and Santarem. Zone America/Porto_Velho -4:15:36 - LMT 1914 -4:00 Brazil AM%sT 1988 Sep 12 -4:00 - AMT # # Roraima (RR) Zone America/Boa_Vista -4:02:40 - LMT 1914 -4:00 Brazil AM%sT 1988 Sep 12 -4:00 - AMT 1999 Sep 30 -4:00 Brazil AM%sT 2000 Oct 15 -4:00 - AMT # # east Amazonas (AM): Boca do Acre, Jutai, Manaus, Floriano Peixoto Zone America/Manaus -4:00:04 - LMT 1914 -4:00 Brazil AM%sT 1988 Sep 12 -4:00 - AMT 1993 Sep 28 -4:00 Brazil AM%sT 1994 Sep 22 -4:00 - AMT # # west Amazonas (AM): Atalaia do Norte, Boca do Maoco, Benjamin Constant, # Eirunepe, Envira, Ipixuna Zone America/Eirunepe -4:39:28 - LMT 1914 -5:00 Brazil AC%sT 1988 Sep 12 -5:00 - ACT 1993 Sep 28 -5:00 Brazil AC%sT 1994 Sep 22 -5:00 - ACT # # Acre (AC) Zone America/Rio_Branco -4:31:12 - LMT 1914 -5:00 Brazil AC%sT 1988 Sep 12 -5:00 - ACT Best Regards ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Vilson farias" <vilson.farias@digitro.com.br> Cc: <pgsql-general@postgresql.org>; "SIMONE Carla MOSENA" <simone.mosena@digitro.com.br>; <paulo.pizarro@digitro.com.br> Sent: Thursday, August 21, 2003 11:40 AM Subject: Re: [GENERAL] timeofday() and CAST > "Vilson farias" <vilson.farias@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 >
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)
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> 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".
BST is not what he was worried about, though. I see the following table
in the zic sources:
# std dst
# -2:00 FNT FNST Fernando de Noronha
# -3:00 BRT BRST Brasilia
# -4:00 AMT AMST Amazon
# -5:00 ACT ACST Acre
I have added BRT, BRST, FNT, FNST to the datetime.c table, since they
don't conflict with any existing entries. ACT and ACST were there
already. We have a problem with AMT/AMST: they conflict with existing
entries for Armenia. Not sure what to do about it, but for the moment
the Armenians were there first.
I suspect in the long run we will have to back off from this fixed-table
approach and allow the set of recognized timezone names to be taken from
a configuration file. There are just too many potential conflicts.
regards, tom lane