On Thu, May 06, 2010 at 04:30:39PM +0100, Cooke, Mark wrote: > > On Thu, May 06, 2010 at 07:02:55AM +0100, Cooke, Mark wrote: > > > > > > > > select id from ticket where current_timestamp - (timestamp with > > > > time zone 'epoch' + time * interval '1 sec') < interval > > '24 hours'; > > > > > > > ...that doesn't look like real sql to me... Could you > > possible provide a > > > working example as I have never managed to get this sort of query to > > > work. > > > > > From: [email protected] On Behalf Of Ross J. Reedstrom > > Sent: 06 May 2010 15:43 > > > > Well, you need to adjust your sense of 'what looks like real > > SQL' then: > > that's a working example cut and paste directly from a psql prompt > > connected to my own trac install. > > > Really? Wow! I did not mean to be rude, I'm still learning sql and > that just looked too much like pseudocode to me that I did not even try > it before replying! > > So... I just tried this as a Trac report: > {{{ > select id, milestone, summary, owner, resolution > from ticket > where current_timestamp - (timestamp with time zone 'epoch' + time * > interval '1 sec') < interval '7 days' > }}} > ...and it works! Fantastic and thanks very much! > > Is any of this postgresql specific?
I actually made that less postgresql specific than my first version. (Postgresql has now() and casts like '01 Jan 2001'::timestamptz) Writing a constant in the SQL spec is defined that way: name of type followed by value, even if the name of type is multi-word. There's an even more verbose CAST ( AS ) syntax I'm pretty sure those are all SQL standard types. The only issue is how exactly "spell" some of the constant values. PostgreSQL does some extended parsing there. If the question is 'does it work in mysql?' you'll have to have someone else answer, I have little experience with it. 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.
