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

Re: Trouble with UNIX TimeStamps

From: Simon Mitchell <pgsql(at)jseb(dot)com>
To: jchum(at)aismedia(dot)com
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Trouble with UNIX TimeStamps
Date: 2002-12-27 16:23:16
Message-ID: 3E0C7E74.5030901@jseb.com (view raw or flat)
Thread:
Lists: pgsql-general
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
>
>  
>



In response to

pgsql-general by date

Next:From: Christoph DalitzDate: 2002-12-27 16:34:46
Subject: Re: Storing passwords
Previous:From: Bruno Wolff IIIDate: 2002-12-27 16:18:11
Subject: Re: Trouble with UNIX TimeStamps

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