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: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
Another way

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


    AND ticket_starters.ticket_time_start > date_part('epoch', now()) 

I did a search and found this solution on this page


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
>	LEFT JOIN ticket_techs ON ticket_techs.queue_id = ticket_queues.queue_id
>	LEFT JOIN ticket_starters ON ticket_starters.queue_id =
>	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?
>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
> / jchum(at)aismedia(dot)com
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (
>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-2017 The PostgreSQL Global Development Group