Postgres Pro
Downloads
Home   >   mailing lists

Thread: EPOCH TIMESTAMP Conversion Problem

EPOCH TIMESTAMP Conversion Problem

From
"Amitanand Chikorde"
Date:
hi all,
   I have a table 'myTable'.   There is an attribute  in the table as,


Column      |            Type             | Modifiers
-----------------+-----------------------------+-----------mydate     | timestamp without time zone | not null


I want to convert MAX(mydate) from myTable to Epoch.


SELECT EXTRACT(EPOCH FROM TIMESTAMP WITHOUT TIME ZONE '2007-11-01
17:42:01.81693') ::int4;


How the above query can be used to convert MAX(mydate) to EPoch value?
Is there any other query that I can use?
I think it is not worth to write a function/procedure to just replace
the value in a query?
    Please HELP...................

Thanks in advance


-- 
Amitanand N. Chikorde.
Software Engineer
==================================
cat /dev/zero >/dev/null
==================================

Re: EPOCH TIMESTAMP Conversion Problem

From
Tom Lane
Date:
"Amitanand Chikorde" <amit99rs@gmail.com> writes:
> I want to convert MAX(mydate) from myTable to Epoch.

Do you meanSELECT EXTRACT(EPOCH FROM MAX(mydate)) FROM myTable
        regards, tom lane

Re: EPOCH TIMESTAMP Conversion Problem

From
Louis-David Mitterrand
Date:
On Wed, Nov 07, 2007 at 10:35:08AM -0500, Tom Lane wrote:
> "Amitanand Chikorde" <amit99rs@gmail.com> writes:
> > I want to convert MAX(mydate) from myTable to Epoch.
> 
> Do you mean
>     SELECT EXTRACT(EPOCH FROM MAX(mydate)) FROM myTable

Is using casts for the same purpose deprecated?
SELECT current_date::timestamp::abstime::int4;

Or less efficient?

Re: EPOCH TIMESTAMP Conversion Problem

From
Tom Lane
Date:
Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> writes:
> On Wed, Nov 07, 2007 at 10:35:08AM -0500, Tom Lane wrote:
>> SELECT EXTRACT(EPOCH FROM MAX(mydate)) FROM myTable

> Is using casts for the same purpose deprecated?

>     SELECT current_date::timestamp::abstime::int4;

Well, type abstime is deprecated --- it's got a Y2038 problem.
        regards, tom lane

Re: EPOCH TIMESTAMP Conversion Problem

From
Michael Glaesemann
Date:
On Jan 1, 2008, at 9:28 , Louis-David Mitterrand wrote:

> On Wed, Nov 07, 2007 at 10:35:08AM -0500, Tom Lane wrote:
>> "Amitanand Chikorde" <amit99rs@gmail.com> writes:
>>> I want to convert MAX(mydate) from myTable to Epoch.
>>
>> Do you mean
>>     SELECT EXTRACT(EPOCH FROM MAX(mydate)) FROM myTable
>
> Is using casts for the same purpose deprecated?
>
>     SELECT current_date::timestamp::abstime::int4;

EXTRACT is SQL-standard syntax which appeals to some people. The ::  
casting syntax is a Postgres extension. And from the docs:

http://www.postgresql.org/docs/8.2/static/datatype-datetime.html

>  The types abstime  and reltime are lower precision types which are  
> used internally. You are discouraged from using these types in new  
> applications and are encouraged to move any old ones over when  
> appropriate. Any or all of these internal types might disappear in  
> a future release.


Michael Glaesemann
grzm seespotcode net