Thread: Trouble with UNIX TimeStamps

Trouble with UNIX TimeStamps

From
"Jonathan Chum"
Date:
Hi Guys,

I'm new to PostGreSQL functions, in particular the date/time functions. I'm
porting my application away from MySQL

The query I'm having trouble converting a mySQL query that looks like this:

SELECT count(*) AS total,
DAYNAME(FROM_UNIXTIME(ticket_starters.ticket_time_start)) AS day FROM
ticket_queues
    LEFT JOIN ticket_techs ON ticket_techs.queue_id = ticket_queues.queue_id
    LEFT JOIN ticket_starters ON ticket_starters.queue_id =
ticket_techs.queue_id
    WHERE
    AND ticket_starters.ticket_time_start > '".(time()-(60*60*24*365))."'
    GROUP BY day, total

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?

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


---
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



Re: Trouble with UNIX TimeStamps

From
Bruno Wolff III
Date:
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)

Re: Trouble with UNIX TimeStamps

From
"Jonathan Chum"
Date:
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



Re: Trouble with UNIX TimeStamps

From
Bruno Wolff III
Date:
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.

Re: Trouble with UNIX TimeStamps

From
will trillich
Date:
> -----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!

Re: Trouble with UNIX TimeStamps

From
Simon Mitchell
Date:
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:

>Hi Guys,
>
>I'm new to PostGreSQL functions, in particular the date/time functions. I'm
>porting my application away from MySQL
>
>The query I'm having trouble converting a mySQL query that looks like this:
>
>SELECT count(*) AS total,
>DAYNAME(FROM_UNIXTIME(ticket_starters.ticket_time_start)) AS day FROM
>ticket_queues
>    LEFT JOIN ticket_techs ON ticket_techs.queue_id = ticket_queues.queue_id
>    LEFT JOIN ticket_starters ON ticket_starters.queue_id =
>ticket_techs.queue_id
>    WHERE
>    AND ticket_starters.ticket_time_start > '".(time()-(60*60*24*365))."'
>    GROUP BY day, total
>
>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?
>
>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
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>---
>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
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>



Re: Trouble with UNIX TimeStamps

From
Ben
Date:
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');




Re: Trouble with UNIX TimeStamps

From
Tom Lane
Date:
"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

Re: Trouble with UNIX TimeStamps

From
Simon Mitchell
Date:
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:

>Hi Guys,
>
>I'm new to PostGreSQL functions, in particular the date/time functions. I'm
>porting my application away from MySQL
>
>The query I'm having trouble converting a mySQL query that looks like this:
>
>SELECT count(*) AS total,
>DAYNAME(FROM_UNIXTIME(ticket_starters.ticket_time_start)) AS day FROM
>ticket_queues
>    LEFT JOIN ticket_techs ON ticket_techs.queue_id = ticket_queues.queue_id
>    LEFT JOIN ticket_starters ON ticket_starters.queue_id =
>ticket_techs.queue_id
>    WHERE
>    AND ticket_starters.ticket_time_start > '".(time()-(60*60*24*365))."'
>    GROUP BY day, total
>
>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?
>
>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
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>---
>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
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>