pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Date Time calculation help
3+ messages / 2 participants
[nested] [flat]

* Date Time calculation help
@ 2002-01-14 14:58 Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>
  2002-01-15 12:39 ` Re: Date Time calculation help Steve Boyle (Roselink) <boylesa@roselink.co.uk>
  0 siblings, 1 reply; 3+ messages in thread

From: Unnikrishnan Menon @ 2002-01-14 14:58 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Date Time calculation help
  2002-01-14 14:58 Date Time calculation help Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>
@ 2002-01-15 12:39 ` Steve Boyle (Roselink) <boylesa@roselink.co.uk>
  0 siblings, 0 replies; 3+ messages in thread

From: Steve Boyle \(Roselink\) @ 2002-01-15 12:39 UTC (permalink / raw)
  To: Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>; pgsql-sql

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:
http://www.il.postgresql.org/users-lounge/docs/7.1/postgres/functions-datetime.html

hih

steve boyle
----- Original Message ----- 
  From: Unnikrishnan Menon 
  To: pgsql-sql@postgresql.org 
  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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Date Time calculation help
@ 2002-01-22 03:29 Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Unnikrishnan Menon @ 2002-01-22 03:29 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-01-22 03:29 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-14 14:58 Date Time calculation help Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>
2002-01-15 12:39 ` Steve Boyle (Roselink) <boylesa@roselink.co.uk>
2002-01-22 03:29 Re: Date Time calculation help Unnikrishnan Menon <unnikrishnan.menon@chennai.transys.net>

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