Skip site navigation (1) Skip section navigation (2)

Re: Trouble with UNIX TimeStamps

From: "Jonathan Chum" <jchum(at)aismedia(dot)com>
To: "'Bruno Wolff III'" <bruno(at)wolff(dot)to>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Trouble with UNIX TimeStamps
Date: 2002-12-27 14:58:14
Message-ID: 035f01c2adb8$62124f30$cd1b9642@atlp.aismedia.com (view raw or flat)
Thread:
Lists: 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(at)aismedia(dot)com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


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


On Fri, Dec 27, 2002 at 08:49:58 -0500,
  Jonathan Chum <jchum(at)aismedia(dot)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



In response to

Responses

pgsql-general by date

Next:From: Campano, TroyDate: 2002-12-27 15:01:30
Subject: Storing passwords
Previous:From: Bruno Wolff IIIDate: 2002-12-27 14:38:07
Subject: Re: Trouble with UNIX TimeStamps

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group