Thread: Datetime problem

O kyrios Eric Lemes egrapse stis Jun 14, 2004 :

> Hello there,
> 
> I'm with a little trouble with postgresql and date/time conversions:
> 
> - 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

Just do select to_timestamp('2004 10 10 00 00 00', 'YYYY MM DD HH MI 
SS')::timestamp
to get rid off timezone info.

> 
> Anybody can help me?
> 
> 
> []'s
> 
> Eric Lemes de Godoy Cintra
> Analista de Sistemas Lνder
> Zanthus Sistemas e Equip. Automaηγo S/A
> Desenvolvimento de Software
> 
> 
> 
> eric.lemes@zanthus.com.br
> Fone: 55(11) 3750-7077
> 
> 
> 
> ---
> 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
> 

-- 
-Achilleus

Hello Eric,

Are you looking for something like :
select to_char(timestamp 'now','YYYY MM DD HH MI SS');
or the values in your example below :
select to_char(timestamp '20041010 00:00:00','YYYY MM DD HH MI SS');

Eric Lemes mentioned :
=> Hello there,
=>
=> I'm with a little trouble with postgresql and date/time conversions:
=>
=> - 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
=>
=> Anybody can help me?
=>
=>
=> []'s
=>
=> Eric Lemes de Godoy Cintra
=> Analista de Sistemas Líder
=> Zanthus Sistemas e Equip. Automação S/A
=> Desenvolvimento de Software
=>
=>
=>
=> eric.lemes@zanthus.com.br
=> Fone: 55(11) 3750-7077
=>
=>
=>
=> ---
=> 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
=>

"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

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

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

"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

"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