Re: Trouble with UNIX TimeStamps

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(at)aismedia(dot)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
>
>
>