pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRES: Datetime problem
4+ messages / 3 participants
[nested] [flat]
* RES: Datetime problem
@ 2004-06-14 16:20 Eric Lemes <eric.lemes@zanthus.com.br>
2004-06-14 17:10 ` Re: RES: Datetime problem Alexander M. Pravking <fduch@antar.bryansk.ru>
2004-06-14 18:47 ` Re: RES: Datetime problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Eric Lemes @ 2004-06-14 16:20 UTC (permalink / raw)
To: pgsql-sql
Hello,
- PostgreSQL 7.3.2 on i386-redhat-linux GCC 3.2.2
- Timezone: Brazil (GMT-3, I think).
I think my problem is with the time zone. Using a SET TIME ZONE GMT, the
result is Ok. But I don't know how to work with time zones correctly.
When I send a date to to_timestamp, pgsql thinks this date is in GMT?
Thanks for all your help.
[]'s
Eric Lemes
> -----Mensagem original-----
> De: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]Em nome de Tom Lane
> Enviada em: segunda-feira, 14 de junho de 2004 11:56
> Para: Eric Lemes
> Cc: pgsql-sql@postgresql.org
> Assunto: Re: [SQL] Datetime problem
>
>
> "Eric Lemes" <eric.lemes@zanthus.com.br> writes:
> > - select to_timestamp('2004 10 10 00 00 00', 'YYYY MM DD HH MI SS')
> > the output is:
> > - 2004-10-09 23:00:00-03
>
> What PG version is this, on what platform, and what's your current
> timezone setting?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.693 / Virus Database: 454 - Release Date: 31/5/2004
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.693 / Virus Database: 454 - Release Date: 31/5/2004
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: RES: Datetime problem
2004-06-14 16:20 RES: Datetime problem Eric Lemes <eric.lemes@zanthus.com.br>
@ 2004-06-14 17:10 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
2004-06-14 19:02 ` Re: RES: Datetime problem Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 4+ messages in thread
From: Alexander M. Pravking @ 2004-06-14 17:10 UTC (permalink / raw)
To: Eric Lemes <eric.lemes@zanthus.com.br>; +Cc: pgsql-sql
On Mon, Jun 14, 2004 at 01:20:14PM -0300, Eric Lemes wrote:
> Hello,
>
> - PostgreSQL 7.3.2 on i386-redhat-linux GCC 3.2.2
> - Timezone: Brazil (GMT-3, I think).
What's about daylight saving time for you?
I'm almost sure the DST boundary is near the date in your example.
However, with 7.3.4 on FreeBSD I get:
fduch=# SHOW TimeZone ;
TimeZone
---------------
Europe/Moscow
(1 row)
fduch=# SELECT to_timestamp('2004 10 31 00 00 00', 'YYYY MM DD HH MI SS');
to_timestamp
------------------------
2004-10-31 00:00:00+04
(1 row)
fduch=# SELECT to_timestamp('2004 11 01 00 00 00', 'YYYY MM DD HH MI SS');
to_timestamp
------------------------
2004-11-01 00:00:00+03
(1 row)
So both timestamps before and after boundary are parsed well for me.
> I think my problem is with the time zone. Using a SET TIME ZONE GMT, the
> result is Ok. But I don't know how to work with time zones correctly.
>
> When I send a date to to_timestamp, pgsql thinks this date is in GMT?
Hmm, 7.3 and 7.4 docs say that it returns timestamp (WITHOUT time zone
is default since 7.3 IIRC), but in fact it accepts and returns timestamp
WITH time zone. This is probably a documentation bug...
--
Fduch M. Pravking
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: RES: Datetime problem
2004-06-14 16:20 RES: Datetime problem Eric Lemes <eric.lemes@zanthus.com.br>
2004-06-14 17:10 ` Re: RES: Datetime problem Alexander M. Pravking <fduch@antar.bryansk.ru>
@ 2004-06-14 19:02 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-06-14 19:02 UTC (permalink / raw)
To: Alexander M. Pravking <fduch@antar.bryansk.ru>; +Cc: Eric Lemes <eric.lemes@zanthus.com.br>; pgsql-sql
"Alexander M. Pravking" <fduch@antar.bryansk.ru> writes:
> Hmm, 7.3 and 7.4 docs say that it returns timestamp (WITHOUT time zone
> is default since 7.3 IIRC), but in fact it accepts and returns timestamp
> WITH time zone. This is probably a documentation bug...
Yeah, it is. Fixed in CVS tip --- thanks for pointing it out.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: RES: Datetime problem
2004-06-14 16:20 RES: Datetime problem Eric Lemes <eric.lemes@zanthus.com.br>
@ 2004-06-14 18:47 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-06-14 18:47 UTC (permalink / raw)
To: Eric Lemes <eric.lemes@zanthus.com.br>; +Cc: pgsql-sql
"Eric Lemes" <eric.lemes@zanthus.com.br> writes:
> - PostgreSQL 7.3.2 on i386-redhat-linux GCC 3.2.2
> - Timezone: Brazil (GMT-3, I think).
Ah, and 2004-10-10 is a daylight savings transition day where you live,
right? (Or at least the obsolete timezone file you have thinks so...)
So local midnight on that day doesn't really exist: you go from 11:59:59
standard time to 01:00:00 summer time.
PG 7.3 and before got this boundary condition wrong in some cases, but
I think 7.4 gets it right. The rule we use now is to interpret invalid
or ambiguous times as local standard time (hence, this input means
midnight standard time), which is what to_timestamp is doing. However,
on reverse conversion you'll get the normalized form of the time, which
is 01:00:00 summer time.
I think what is happening in 7.3 is that the input is interpreted as
midnight summer time, which would reverse-convert as 11:00 PM standard
time...
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-06-14 19:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-14 16:20 RES: Datetime problem Eric Lemes <eric.lemes@zanthus.com.br>
2004-06-14 17:10 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
2004-06-14 19:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-14 18:47 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox