Edgewall Software

Opened 15 years ago

Closed 15 years ago

#2219 closed defect (worksforme)

Bad result from "date(changetime)" in report sql statement

Reported by: anonymous Owned by: daniel
Priority: normal Milestone:
Component: report system Version: 0.8.4
Severity: normal Keywords:
Cc: Branch:
Release Notes:
API Changes:
Internal Changes:


The following SQL statement in my report produces a incorrect output for the date of the changetime field:

SELECT id AS ticket, date(changetime) AS 'Date Closed',

summary, component, time AS created FROM ticket WHERE status = 'closed' ORDER BY changetime DESC

For example, the 'Date Closed' column in the report will contain "3086946-03-20" for the date, expecting something like "2005-10-13".

When using "SELECT id AS ticket, changetime AS 'Date Closed'" instead, the result is "1129205013", which is expected for a Unix Timestamp.

Cheers, Bruce Clark SAIC 410-865-7661

Attachments (0)

Change History (2)

comment:1 by anonymous, 15 years ago

Forgot to add my email address. Bruce.A.Clark@…

comment:2 by Matthew Good, 15 years ago

Resolution: worksforme
Status: newclosed

In the future please use the MailingList or IrcChannel to ask for support on writing reports instead of filing a ticket, since this doesn't really represent a bug in Trac.

You need to let SQLite know what format the date is in, which can be done like:

select date(changetime, 'unixepoch') from ticket

Or if you need to specify an alternate format:

select strftime('%m/%d/%Y', changetime, 'unixepoch') from ticket

Refer to the SQLite documentation for more information on the date and time functions: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Modify Ticket

Change Properties
Set your email in Preferences
as closed The owner will remain daniel.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from daniel to the specified user.

Add Comment

E-mail address and name can be saved in the Preferences .
Note: See TracTickets for help on using tickets.