Postgres Pro
Downloads
Home   >   mailing lists

Thread: Date Time calculation help

Date Time calculation help

From
"Unnikrishnan Menon"
Date:
Hi,
 
I have a particular query in Oracle :
 
select mod((sysdate - to_date('15-JAN-02','dd-mon-yy')),1) from dual;
The above query gives 0.752.. as the difference between the two days which when multiplied by 24 gives around 3.1.. hrs.
 
I tried the same query in postgresql but the value returned is 0.
The query was:
select mod((current_date - '2002-15-01')),1);
 
I require the difference in time to the last second to be shown as time left. Could anyone suggest as to how to overcome this particular problem.
 
Thanx in advance,
 
Unni

Re: Date Time calculation help

From
"Steve Boyle \(Roselink\)"
Date:
Unnikrishanan,
 
The following query will return the number of whole days between the two dates (a working version of postgres query you posted).
 
select date_part('day','now'::datetime - '2001-01-15'::datetime);
Other available datetime functions can be found at:
 
hih
 
steve boyle
----- Original Message -----
Sent: Monday, January 14, 2002 2:58 PM
Subject: [SQL] Date Time calculation help

Hi,
 
I have a particular query in Oracle :
 
select mod((sysdate - to_date('15-JAN-02','dd-mon-yy')),1) from dual;
The above query gives 0.752.. as the difference between the two days which when multiplied by 24 gives around 3.1.. hrs.
 
I tried the same query in postgresql but the value returned is 0.
The query was:
select mod((current_date - '2002-15-01')),1);
 
I require the difference in time to the last second to be shown as time left. Could anyone suggest as to how to overcome this particular problem.
 
Thanx in advance,
 
Unni

Re: Date Time calculation help

From
"Unnikrishnan Menon"
Date:
Thanx Josh,

This helped solve my problem.

Unni
----- Original Message ----- 
From: "Josh Berkus" <josh@agliodbs.com>
To: "Unnikrishnan Menon" <unnikrishnan.menon@chennai.transys.net>
Sent: Tuesday, January 22, 2002 2:02 AM
Subject: Re: [SQL] Date Time calculation help


> Unni,
> 
> > I tried the same query in postgresql but the value returned is 0.
> > The query was:
> > select mod((current_date - '2002-15-01')),1);
> 
> The problem above is that you are using the DATE data type, which does
>  not admit hours.  As a result, mod(1)=0 is quite correct.
> 
> You want to use the TIMESTAMP data type, and process your outcome as an
>  interval:
> select (current_timestamp - ('2002-15-01'::Timestamp))
> which should give you some days and some hours.
> 
> See: http://techdocs.postgresql.org/techdocs/faqdatesintervals.php
> for more info about dates and times.
> 
> Of course, I could give you more help if I was clear on what exactly
>  you want to achieve.  Do you want the date, or just hours? Care to
>  elucidate?
> 
> -Josh Berkus
> 
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco