Обсуждение: 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
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