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)

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

Another way

You could replace
AND ticket_starters.ticket_time_start > '".(time()-(60*60*24*365))."'

with

AND ticket_starters.ticket_time_start > date_part('epoch', now())
-(60*60*24*365)

I did a search and found this
http://www.archonet.com/pgdocs/date-to-epoch.html

Regards,
Simon

Jonathan Chum wrote:

-----Original Message-----
Sent: Friday, December 27, 2002 9:38 AM

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

On Fri, Dec 27, 2002 at 09:58:14AM -0500, Jonathan Chum wrote:

Sorry, I'm still to new with using functions within PostGreSQL, but . . .
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?

SELECT
to_char('epoch'::timestamp +
(a_table.unix_seconds_field || ' seconds')::interval,
'Day')
WHERE
some.condition > particular.thingie
;

i think...

--
"We will fight them on the beaches, we will fight them on the
sons of beaches" -- Miguel Churchill, Winston's bastard Mexican
brother.
--lifted from http://www.astray.com/acmemail/stable/documentation.xml

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

On Fri, Dec 27, 2002 at 09:58:14 -0500,
Jonathan Chum <jchum@aismedia.com> wrote:

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

You mixed up what needed to be replaced in the example. Try something like:
SELECT to_char('epoch'::timestamp + (ticket_starters.ticket_time_start ||
' seconds')::interval, 'Day') FROM ticket_starters;

I haven't tested this example so I may have made a typo.

What this is doing is using the to_char function to print the day of the
week corresponding to the calculated timestamp.
Since what you have is an integer offset from the unix epoch. I add the
offset to the timestamp corresponding to the epoch to get the desired
timestamp. In 7.3 there isn't an integer to interval conversion function
(there may have been one earlier that assumed the integer was the number
of seconds), so I build a text string suitable for converting to interval.
Since unix time returns seconds from the epoch, I specify that the number
used for the interval is in seconds.

Another way

You could replace
AND ticket_starters.ticket_time_start > '".(time()-(60*60*24*365))."'

with

AND ticket_starters.ticket_time_start > date_part('epoch', now())
-(60*60*24*365)

I did a search and found this solution on this page
http://www.archonet.com/pgdocs/date-to-epoch.html

Regards,
Simon

Jonathan Chum wrote:

On Fri, 2002-12-27 at 05:49, Jonathan Chum 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?

select 1041013653::int4::abstime;

will give you the timestamp from unixtime. If you just want the day, you
can use to_char like so:

select to_char(1041013653::int4::abstime,'dd');

"Jonathan Chum" <jchum@aismedia.com> writes:

ERROR: Cannot cast type integer to timestamp with time zone

A solution with less notational cruft is to cast the integer to abstime:

regression=# select 1040999196::abstime;
abstime
------------------------
2002-12-27 09:26:36-05
(1 row)

regression=# select to_char(1040999196::abstime, 'Day');
to_char
-----------
Friday
(1 row)

abstime is a deprecated datatype if you believe the manual, but I
seriously doubt that we'll remove it anytime soon. Maybe when Y2038
is upon us (by which time you'd better have found another representation
for your table, anyway).

regards, tom lane