Emmanuel,
I was not able to get the Report to work exactly as you illustrated, but
you definitely got me on the right path for a solution. We are using
postgres DB ... not sure if your solution was DB specific .. ie sqllite ..
etc ?
Here is what I ended up with ... perhaps not as graceful ... but it
works .... Many Thanks !!
List ALL tickets Created Since YYYY-MM-DD HH:MM:SS ordered by ID in
DESC Seq
===============================================================================
SELECT id AS ticket, status, type, component, severity, priority, owner,
TIME as created, summary FROM ticket
WHERE status IN ('new', 'assigned', 'reopened', 'closed')
AND TIME >
(SELECT EXTRACT(EPOCH FROM TIMESTAMP '2008-05-23 00:00:00'))
ORDER BY id desc
or
List ALL tickets Created Between YYYY-MM-DD HH:MM:SS and YYYY-MM-DD
HH:MM:SS ordered by ID in ASC Seq
=======================================================================================================================
SELECT id AS ticket, status, type, component, severity, priority, owner,
TIME as created, summary FROM ticket
WHERE status IN ('new', 'assigned', 'reopened', 'closed')
AND TIME >
(SELECT EXTRACT(EPOCH FROM TIMESTAMP '2008-04-23 00:00:00'))
AND TIME <
(SELECT EXTRACT(EPOCH FROM TIMESTAMP '2008-05-23 00:00:00'))
ORDER BY id ASC
Best Regards,
Joe
________________________________________________________________________________________
Joseph H. Dayney | Software Engineer | RR Donnelley
630W 1000N | Logan, UT 84321 | (: 435-755-4278 | 801-453-5015 |È:
435-770-7244 | Ê: 435-755-4210 | *: [EMAIL PROTECTED]
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
Any unauthorized review, use, disclosure or distribution is prohibited. If
you are not the intended recipient, please contact the sender by reply
e-mail and destroy all copies of the original message.
Emmanuel <[EMAIL PROTECTED]>
05/28/2008 03:47 PM
To
[EMAIL PROTECTED]
cc
Subject
Re: [Trac] Re: Formatting dates in reports
Joseph,
- If you want to display the time in %Y%m%d-%H%M format, then replace "
time as created " in your SELECT block by " strftime('%Y%m%d %H:%M',
datetime(time, 'unixepoch')) AS Opened "
(do not use the word "created" as it won't work, that's why I replaced by
"opened").
- If you want to use a human time in your WHERE clause, replace " time >
'1208131200' " by " time > strftime('%s',datetime("now","-2 days"))+0 "
(in this example it means "less than 2 days old")
(I got this from http://trac.edgewall.org/ticket/2288)
Hope this helps
Regards
Emmanuel
[EMAIL PROTECTED] wrote:
Emmanuel,
Your help please ... similar issue ... you seemed to get it working ....
My CURRENT Query
All Tickets CREATED since ... 04-14-2008 00:00:00 MM-DD-YYYY
HH:MM:SS
SELECT id AS ticket, status, type, component, severity, priority, owner,
time as created, summary FROM ticket
WHERE status IN ('new', 'assigned', 'reopened', 'closed')
/* Unix Time No of Secs since Epoch */
AND time > '1208131200'
ORDER BY id desc
I am trying to create a Report that uses the "CURRENT" DATE TIME as the
argument .. or at least a HUMAN Time format ... such as %Y%m%d-%H%M ...
Any suggestions please ?
Best Regards,
Joe
________________________________________________________________________________________
Joseph H. Dayney | Software Engineer | RR Donnelley
630W 1000N | Logan, UT 84321 | (: 435-755-4278 | 801-453-5015 |È:
435-770-7244 | Ê: 435-755-4210 | *: [EMAIL PROTECTED]
This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
Any unauthorized review, use, disclosure or distribution is prohibited. If
you are not the intended recipient, please contact the sender by reply
e-mail and destroy all copies of the original message.
emx <[EMAIL PROTECTED]>
Sent by: [email protected]
05/28/2008 01:15 PM
Please respond to
[email protected]
To
[email protected]
cc
Subject
[Trac] Re: Formatting dates in reports
Thanks Noah that did the trick. I just changed to:
strftime('%Y%m%d-%H%M', datetime(time, 'unixepoch'))
and it started to work like a charm.
"created" is a magic column name in reports, that triggers automated
formatting. This formatting requires the row values to be epoch time
stamps
(seconds since Jan 1, 1970 00:00:00). You will need use a different column
name.
--Noah
--
View this message in context:
http://www.nabble.com/Formatting-dates-in-reports-tp17518209p17520020.html
Sent from the Trac Users mailing list archive at Nabble.com.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
