Обсуждение: date time function

Hi,

I have a timestamp field in my talbe.
I need to check its difference in days with the current date.

field name is time_stamp and I did it as follows:

select age(timestamp '2000-06-28 15:39:47.272045 ')

it gives me something like

6 years 11 mons 29 days 08:20:12.727955

How can i convert this result into absolute number of days.

thanks,

~Jas

On 28/06/2007 21:04, Jasbinder Singh Bali wrote:

> How can i convert this result into absolute number of days.

Cast your result to type INTERVAL - something like this:

postgres=# select (current_timestamp - timestamp '2007-05-01')::interval;

        interval
----------------------
  58 days 21:10:36.748
(1 row)

Of course, you'll need to decide how to handle the part of a day left over.

HTH,

Ray.

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

On Jun 28, 2007, at 15:13 , Raymond O'Donnell wrote:

> Cast your result to type INTERVAL - something like this:
>
> postgres=# select (current_timestamp - timestamp
> '2007-05-01')::interval;
>
>        interval
> ----------------------
>  58 days 21:10:36.748
> (1 row)

The cast to interval is superfluous: timestamp - timestamp already
gives you an interval result. Also, Postgres will interpret
'2007-05-01' as a date in this context:

# select current_timestamp - '2007-05-01';
         ?column?
-------------------------
58 days 16:25:53.776695
(1 row)

Michael Glaesemann
grzm seespotcode net

am  Thu, dem 28.06.2007, um 16:04:48 -0400 mailte Jasbinder Singh Bali folgendes:
> Hi,
>
> I have a timestamp field in my talbe.
> I need to check its difference in days with the current date.
>
> field name is time_stamp and I did it as follows:
>
> select age(timestamp '2000-06-28 15:39:47.272045 ')
>
> it gives me something like
>
> 6 years 11 mons 29 days 08:20:12.727955
>
> How can i convert this result into absolute number of days.

test=*# select current_date - '2000-06-28 15:39:47.272045 '::date;
 ?column?
----------
     2557
(1 row)



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

I can't anything in the docs that explain how intervals print out.
They seem to show like this:

 > select now() - '1990-01-01';
            ?column?
-------------------------------
6388 days 13:06:26.3605600595

or like this:

 > select now() - current_date;
     ?column?
-----------------
14:06:46.119788

unless you use age(), which supposedly also returns an interval:

 > select age(now(), '1990-01-01');
                    age
-----------------------------------------
17 years 5 mons 28 days 14:08:04.524803

Why do the first and third intervals print out differently?  I see
this in the docs for age:

   Subtract arguments, producing a "symbolic" result that uses years
and months

But age() is documented as simply producing an interval - where is
the magic that makes the first and third results above look
different?  Ah, wait a minute - does this have to do with the varying
number of days in different months?

Thanks.

- John D. Burger
   MITRE

"John D. Burger" <john@mitre.org> writes:
> Why do the first and third intervals print out differently?

The underlying storage is months, days, and seconds --- "1 year"
is the same as "12 months", but not the same as "365 days".
IIRC plain timestamp subtraction produces an interval with days and
seconds but the month part is always 0.  You can use justify_days
to convert days to months (at an assumed 30 days/month).  I'm not
sure exactly what age() does but it's probably producing a value
that has nonzero months to start with.

            regards, tom lane

On Jun 29, 2007, at 13:17 , John D. Burger wrote:

> I can't anything in the docs that explain how intervals print out.
> They seem to show like this:
>
> > select now() - '1990-01-01';
>            ?column?
> -------------------------------
> 6388 days 13:06:26.3605600595

Without being anchored with a timestamp, we have no way to know how
long a given month is in the result, so it plays it safe by returning
everything in days.

> or like this:
>
> > select now() - current_date;
>     ?column?
> -----------------
> 14:06:46.119788
>
> unless you use age(), which supposedly also returns an interval:
>
> > select age(now(), '1990-01-01');
>                    age
> -----------------------------------------
> 17 years 5 mons 28 days 14:08:04.524803
>
> Why do the first and third intervals print out differently?

The timestamp[tz]_age functions currently don't use the same
algorithm the timestamp_mi code does. This should probably be
reconciled in the future so results are consistent.

> But age() is documented as simply producing an interval - where is
> the magic that makes the first and third results above look different?

src/backend/utils/adt/timestamp.c

> Ah, wait a minute - does this have to do with the varying number of
> days in different months?

Yes.

Michael Glaesemann
grzm seespotcode net