Hi,

How about adding these for 7.3?  Can this be put in the TODO?

EXTRACT(TIMESTAMP FROM epoch);
EXTRACT(DATE FROM epoch);
EXTRACT(DOW FROM epoch);
...

etc.

Would be very useful.

Chris


Thu, 7 Feb 2002 11:18:20 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Suggestions for 7.3 date handling
> How about adding these for 7.3?  Can this be put in the TODO?
> 
> EXTRACT(TIMESTAMP FROM epoch);
> EXTRACT(DATE FROM epoch);
> EXTRACT(DOW FROM epoch);
> ...

What do you want this to do exactly?

                    - Thomas

Thu, 07 Feb 2002 05:15:15 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Suggestions for 7.3 date handling
> > How about adding these for 7.3?  Can this be put in the TODO?
> >
> > EXTRACT(TIMESTAMP FROM epoch);
> > EXTRACT(DATE FROM epoch);
> > EXTRACT(DOW FROM epoch);
> > ...
>
> What do you want this to do exactly?

OK, we have some legacy columns that use int4 as their type.  It would be
nice to be able to do easy date handling with them.

eg. EXTRACT(TIMESTAMP FROM EPOCH '1081237846')

Chris


Thu, 7 Feb 2002 13:27:27 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Suggestions for 7.3 date handling
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> What do you want this to do exactly?

> OK, we have some legacy columns that use int4 as their type.  It would be
> nice to be able to do easy date handling with them.

Cast to abstime.

			regards, tom lane

Thu, 07 Feb 2002 11:12:48 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Suggestions for 7.3 date handling 
Christopher Kings-Lynne writes:

> OK, we have some legacy columns that use int4 as their type.  It would be
> nice to be able to do easy date handling with them.
>
> eg. EXTRACT(TIMESTAMP FROM EPOCH '1081237846')

timestamp 'epoch' + interval '1 second' * your_int

-- 
Peter Eisentraut   peter_e@gmx.net


Thu, 7 Feb 2002 11:43:42 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Suggestions for 7.3 date handling
(resent, with changes)

> OK, we have some legacy columns that use int4 as their type.  It would be
> nice to be able to do easy date handling with them.

How about this? Folding in Peter's suggestion to use a multiplication
operator rather than a text string conversion which I originally
proposed:

thomas=# create or replace function date_part(text,int4)
thomas-# returns float8 as
thomas-# 'select date_part($1, timestamp without time zone \'epoch\'
thomas-# + (interval '1 sec' * $2));' language 'sql';

thomas=# select extract('epoch' from timestamp without time zone
'today'),
thomas-# extract('epoch' from 1013040000);
 date_part  | date_part  
------------+------------
 1013040000 | 1013040000

Seems to provide what you want, and you don't have to do any coding.

btw, I like that "create or replace" we have now!

                        - Thomas

Thu, 07 Feb 2002 17:29:38 +0000
Thomas Lockhart <lockhart@fourpalms.org>
Re: Suggestions for 7.3 date handling
Thomas Lockhart <lockhart@fourpalms.org> writes:
> thomas=# create or replace function date_part(text,int4)
> thomas-# returns float8 as
> thomas-# 'select date_part($1, timestamp without time zone \'epoch\'
> thomas-# + (interval '1 sec' * $2));' language 'sql';

Or just

regression=# create or replace function date_part(text,int4)
regression-# returns float8 as
regression-# 'select date_part($1, $2::abstime::timestamp)'
regression-# language sql;

Thomas, of course, would really like to get rid of type abstime,
but it's so dang useful (for exactly this reason) that I don't
expect it to disappear until Unixen move away from 4-byte time_t.

			regards, tom lane

Thu, 07 Feb 2002 12:58:50 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Suggestions for 7.3 date handling 