Thread: Suggestions for 7.3 date handling

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

> 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

> > 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

"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

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

(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

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