Re: Trouble with UNIX TimeStamps

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