Re: Trouble with UNIX TimeStamps - Mailing list pgsql-general

Sorry, I'm still to new with using functions within PostGreSQL, but . . .
I've tried:

SELECT to_char((1040999196 || ' seconds')::interval +
ticket_starters.ticket_time_start::timestamptz,'Day') FROM ticket_starters;

and it returned back:

ERROR:  Cannot cast type integer to timestamp with time zone

So constructed antoher query:

SELECT to_char((1040999196 || ' seconds')::interval +
ticket_starters.ticket_time_start::timestamp ,'Day') FROM ticket_starters;

and it returned back:

ERROR:  Cannot cast type integer to timestamp without time zone

In my table, the column, ticket_time_start has a INTEGER value of
'1009462540' which is today's date.

How'd would I construct the query to pull from the db?

Regards,
Jonathan Chum
Systems Developer

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
A I S   M e d i a ,  I n c .
"We Build eBusinesses"
115 Perimeter Center Terrace
Suite 540
Atlanta, GA 30346
Tel: 800.784.0919, Ext 502 / Fax: 678.382.2471
http://www.aismedia.com / jchum@aismedia.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bruno Wolff III
Sent: Friday, December 27, 2002 9:38 AM
To: Jonathan Chum
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Trouble with UNIX TimeStamps


On Fri, Dec 27, 2002 at 08:49:58 -0500,
  Jonathan Chum <jchum@aismedia.com> wrote:
>
> The table column, ticket_starters.ticket_time_start is an INT type which
> contains a UNIX timestamp. I did not see anywhere in the Interactive docs
> how'd I convert the UNIX timestamp into a timestamp type so I can extract
> the day name. I'd rather not use PostGreSQL's timestamp types and just
> convert the database over to it since much of the programming utilizes the
> UNIX timestamp.  Any ideas?

One way to do this is:
area=> select to_char('epoch'::timestamp + (1040999196 || '
seconds')::interval,'Day');
  to_char
-----------
 Friday
(1 row)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.427 / Virus Database: 240 - Release Date: 12/6/02

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.427 / Virus Database: 240 - Release Date: 12/6/02