On Wed, May 05, 2010 at 05:01:20PM +0100, aidy lewis wrote:
> Hi,
>
> I am using a postgres db and I am trying to select specific changed
> states that occur today.
>
> SELECT id AS ticket, status, summary
> FROM ticket
> WHERE status IN ('new', 'closed', 'reopened')
>
> /* AND THE ABOVE STATE CHANGES OCCCURRED TODAY */
>
> I think I am having issues comparing a unix timestamp with current_date
Yeah, trac not using a timestamptz data time for times is a bit bogus.
Anyway, the magic for conversion is to add the seconds interval to the
epoch:
select id from ticket where current_timestamp - (timestamp with
time zone 'epoch' + time * interval '1 sec') < interval '24 hours';
Is it possible also to COUNT the number of results returned?
select count(*) from ticket where current_timestamp - (timestamp with
time zone 'epoch' + time * interval '1 sec') < interval '24 hours';
Ross
--
Ross Reedstrom, Ph.D. [email protected]
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
The Connexions Project http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
--
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/trac-users?hl=en.
