Thread: Problem working with dates and times.

On 4 Dec 2006 at 20:13, Alejandro Michelin Salomon ( Adinet ) wrote:

> But i only need 2006-12-05 from the resulting timestamp.
> How to cut only the date from this timestamp?

Use date_trunc() just to lop off the time part of the timestamp:

http://www.postgresql.org/docs/8.2/static/functions-
datetime.html#FUNCTIONS-DATETIME-TRUNC

Alternatively, use to_char() to format the output exactly as you'd
like it:

http://www.postgresql.org/docs/8.2/static/functions-formatting.html


--Ray.


----------------------------------------------------------------------

Raymond O'Donnell
Director of Music, Galway Cathedral, Galway, Ireland
rod@iol.ie
----------------------------------------------------------------------

On Dec 5, 2006, at 8:13 , Alejandro Michelin Salomon (( Adinet )) wrote:

> This table has :
>
> Inicial date
> Inicial hour
> Duration
> Final Date
> Final time
>
> Final Date and Final time are calculate based in Inicial date,
> Inicial hour, Duration.

<snip />

> But i only need 2006-12-05 from the resulting timestamp.
>
> How to cut only the date from this timestamp?

# select cast('2006-12-04 20:00'::timestamp + interval '5 hours' as
date);
     date
------------
2006-12-05
(1 row)

That should do what you want.

I'd suggest changing your schema a little bit to remove the derived
columns.

inicial_timestamp timestamp with time zone
duration interval

I'd also recommend using timestamp with time zone, as it uniquely
identifies a global time.

Inicial date, inicial hour, final date, and final hour can be derived
from these two columns, e.g..

select cast('2006-12-04 20:00'::timestamptz + interval '5 hours' as
time);
    time
----------
01:00:00
(1 row)

This also saves you the need to check that final hour and final date
columns are correct compared to the inicial date, inicial hour, and
duration columns.

Hope this helps.

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann wrote:
-->-----Mensagem original-----
-->De: Michael Glaesemann [mailto:grzm@seespotcode.net]
-->Enviada em: segunda-feira, 4 de dezembro de 2006 19:44
-->Para: Alejandro Michelin Salomon ( Adinet )
-->Cc: Pgsql-General
-->Assunto: Re: [GENERAL] Problem working with dates and times.
-->
-->
-->
-->On Dec 5, 2006, at 8:13 , Alejandro Michelin Salomon ((
-->Adinet )) wrote:
-->
-->> This table has :
-->>
-->> Inicial date
-->> Inicial hour

-->> Duration
-->> Final Date
-->> Final time
-->>
-->> Final Date and Final time are calculate based in Inicial date,
-->> Inicial hour, Duration.
-->
--><snip />
-->
-->> But i only need 2006-12-05 from the resulting timestamp.
-->>
-->> How to cut only the date from this timestamp?
-->
--># select cast('2006-12-04 20:00'::timestamp + interval '5 hours' as
-->date);
-->     date
-->------------
-->2006-12-05
-->(1 row)
-->
-->That should do what you want.
-->
-->I'd suggest changing your schema a little bit to remove the derived
-->columns.
-->
-->inicial_timestamp timestamp with time zone
-->duration interval
-->
-->I'd also recommend using timestamp with time zone, as it uniquely
-->identifies a global time.
-->
-->Inicial date, inicial hour, final date, and final hour can
-->be derived
-->from these two columns, e.g..
-->
-->select cast('2006-12-04 20:00'::timestamptz + interval '5 hours' as
-->time);
-->    time
-->----------
-->01:00:00
-->(1 row)
-->
-->This also saves you the need to check that final hour and
-->final date
-->columns are correct compared to the inicial date, inicial hour, and
-->duration columns.
-->
-->Hope this helps.
-->
-->Michael Glaesemann
-->grzm seespotcode net
-->
-->

Thanks Michael for the help.

Alejandro Michelin Salomon.