pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDate interval
8+ messages / 7 participants
[nested] [flat]
* Date interval
@ 2003-09-26 05:18 Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-26 05:57 ` Re: Date interval Tomasz Myrta <jasiek@klaster.net>
2003-09-26 06:16 ` Re: Date interval sad <sad@bankir.ru>
0 siblings, 2 replies; 8+ messages in thread
From: Richard Sydney-Smith @ 2003-09-26 05:18 UTC (permalink / raw)
To: pgsql-sql
I am looking for a way to convert an interval into a number of days ( integer);
In sybase I would use :
days(today()-r_expire)
where r_expire is the timestamp I am comparing against.
How do we do this in postgres?
date_trunc(today()-r_expire) does not seem to do it
thanks
Richard
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Date interval
2003-09-26 05:18 Date interval Richard Sydney-Smith <richard@ibisaustralia.com>
@ 2003-09-26 05:57 ` Tomasz Myrta <jasiek@klaster.net>
1 sibling, 0 replies; 8+ messages in thread
From: Tomasz Myrta @ 2003-09-26 05:57 UTC (permalink / raw)
To: Richard Sydney-Smith <richard@ibisaustralia.com>; +Cc: pgsql-sql
> I am looking for a way to convert an interval into a number of days (
> integer);
>
> In sybase I would use :
>
> days(today()-r_expire)
>
> where r_expire is the timestamp I am comparing against.
>
> How do we do this in postgres?
>
> date_trunc(today()-r_expire) does not seem to do it
select extract(epoch from some_interval)/3600/24::integer
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Date interval
2003-09-26 05:18 Date interval Richard Sydney-Smith <richard@ibisaustralia.com>
@ 2003-09-26 06:16 ` sad <sad@bankir.ru>
1 sibling, 0 replies; 8+ messages in thread
From: sad @ 2003-09-26 06:16 UTC (permalink / raw)
To: pgsql-sql
On Friday 26 September 2003 09:18, you wrote:
> I am looking for a way to convert an interval into a number of days (
> integer);
>
> In sybase I would use :
>
> days(today()-r_expire)
SELECT now()::DATE - '1900-12-10'::DATE;
?column?
--------------------------------
37545
SELECT now()-'1900-12-10'::timestamp;
?column?
--------------------------------
37545 days 06:10:54.2021608353
^ permalink raw reply [nested|flat] 8+ messages in thread
* Date Interval
@ 2005-12-06 13:49 Magdalena Komorowska <meluzyna@w3.net.pl>
2005-12-06 16:54 ` Re: Date Interval Bruce Momjian <pgman@candle.pha.pa.us>
2005-12-06 17:46 ` Re: Date Interval Magdalena Komorowska <meluzyna@w3.net.pl>
0 siblings, 2 replies; 8+ messages in thread
From: Magdalena Komorowska @ 2005-12-06 13:49 UTC (permalink / raw)
To: pgsql-sql
Hi,
I hale a problem with counting interwal and I can't find what to do with
this.
I have two fields in the table:
Column | Type | Modifiers
-----------------+---------+-----------
date_in | date |
interwal_months | numeric |
-----------------+---------+-----------
Query
SELECT date_in + INTERVAL '3 MONTH' FROM any_table
works fine of course.
However, how to do something like that?
SELECT date_in + INTERVAL ' interwal_months MONTH' FROM any_table
I hope I'm just blind.. ;-)
Thanks for any help.
MK
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Date Interval
2005-12-06 13:49 Date Interval Magdalena Komorowska <meluzyna@w3.net.pl>
@ 2005-12-06 16:54 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-12-06 17:09 ` Re: Date Interval Michael Fuhr <mike@fuhr.org>
2005-12-06 17:15 ` Re: Date Interval Harald Fuchs <hf0923x@protecting.net>
1 sibling, 2 replies; 8+ messages in thread
From: Bruce Momjian @ 2005-12-06 16:54 UTC (permalink / raw)
To: Magdalena Komorowska <meluzyna@w3.net.pl>; +Cc: pgsql-sql
Magdalena Komorowska wrote:
> Hi,
> I hale a problem with counting interwal and I can't find what to do with
> this.
> I have two fields in the table:
> Column | Type | Modifiers
> -----------------+---------+-----------
> date_in | date |
> interwal_months | numeric |
> -----------------+---------+-----------
>
> Query
> SELECT date_in + INTERVAL '3 MONTH' FROM any_table
> works fine of course.
>
> However, how to do something like that?
> SELECT date_in + INTERVAL ' interwal_months MONTH' FROM any_table
How about this?
test=> CREATE TABLE test(x TEXT);
CREATE TABLE
test=> INSERT INTO test VALUES ('3');
INSERT 0 1
test=> SELECT current_timestamp + cast(x || ' months' AS INTERVAL) FROM
test;
?column?
-------------------------------
2006-03-06 11:53:05.574279-05
(1 row)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Date Interval
2005-12-06 13:49 Date Interval Magdalena Komorowska <meluzyna@w3.net.pl>
2005-12-06 16:54 ` Re: Date Interval Bruce Momjian <pgman@candle.pha.pa.us>
@ 2005-12-06 17:09 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 8+ messages in thread
From: Michael Fuhr @ 2005-12-06 17:09 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Magdalena Komorowska <meluzyna@w3.net.pl>; pgsql-sql
On Tue, Dec 06, 2005 at 11:54:05AM -0500, Bruce Momjian wrote:
> test=> SELECT current_timestamp + cast(x || ' months' AS INTERVAL) FROM
> test;
> ?column?
> -------------------------------
> 2006-03-06 11:53:05.574279-05
> (1 row)
Or another way:
test=> CREATE TABLE test (x numeric);
CREATE TABLE
test=> INSERT INTO test VALUES (3);
INSERT 0 1
test=> SELECT current_timestamp + x * interval'1 month' FROM test;
?column?
-------------------------------
2006-03-06 12:07:48.112765-05
(1 row)
--
Michael Fuhr
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Date Interval
2005-12-06 13:49 Date Interval Magdalena Komorowska <meluzyna@w3.net.pl>
2005-12-06 16:54 ` Re: Date Interval Bruce Momjian <pgman@candle.pha.pa.us>
@ 2005-12-06 17:15 ` Harald Fuchs <hf0923x@protecting.net>
1 sibling, 0 replies; 8+ messages in thread
From: Harald Fuchs @ 2005-12-06 17:15 UTC (permalink / raw)
To: pgsql-sql
In article <200512061654.jB6Gs5d14178@candle.pha.pa.us>,
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Magdalena Komorowska wrote:
>> Hi,
>> I hale a problem with counting interwal and I can't find what to do with
>> this.
>> I have two fields in the table:
>> Column | Type | Modifiers
>> -----------------+---------+-----------
>> date_in | date |
>> interwal_months | numeric |
>> -----------------+---------+-----------
>>
>> Query
>> SELECT date_in + INTERVAL '3 MONTH' FROM any_table
>> works fine of course.
>>
>> However, how to do something like that?
>> SELECT date_in + INTERVAL ' interwal_months MONTH' FROM any_table
> How about this?
> test=> CREATE TABLE test(x TEXT);
> CREATE TABLE
> test=> INSERT INTO test VALUES ('3');
> INSERT 0 1
> test=> SELECT current_timestamp + cast(x || ' months' AS INTERVAL) FROM
> test;
> ?column?
> -------------------------------
> 2006-03-06 11:53:05.574279-05
> (1 row)
Since Magdalena doesn't store the number of months in a string, the
following might be more convenient:
SELECT date_in + interwal_months * INTERVAL '1 MONTH' FROM any_table
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Date Interval
2005-12-06 13:49 Date Interval Magdalena Komorowska <meluzyna@w3.net.pl>
@ 2005-12-06 17:46 ` Magdalena Komorowska <meluzyna@w3.net.pl>
1 sibling, 0 replies; 8+ messages in thread
From: Magdalena Komorowska @ 2005-12-06 17:46 UTC (permalink / raw)
To: pgsql-sql
It works great, very nice method :-)
thanks a lot!
MK
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-12-06 17:46 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-26 05:18 Date interval Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-26 05:57 ` Tomasz Myrta <jasiek@klaster.net>
2003-09-26 06:16 ` sad <sad@bankir.ru>
2005-12-06 13:49 Date Interval Magdalena Komorowska <meluzyna@w3.net.pl>
2005-12-06 16:54 ` Re: Date Interval Bruce Momjian <pgman@candle.pha.pa.us>
2005-12-06 17:09 ` Re: Date Interval Michael Fuhr <mike@fuhr.org>
2005-12-06 17:15 ` Re: Date Interval Harald Fuchs <hf0923x@protecting.net>
2005-12-06 17:46 ` Re: Date Interval Magdalena Komorowska <meluzyna@w3.net.pl>
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