Обсуждение: 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
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 -----
From: Unnikrishnan MenonSent: Monday, January 14, 2002 2:58 PMSubject: [SQL] Date Time calculation helpHi,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
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