Edgewall Software

Opened 9 years ago

Closed 9 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:
Release Notes:
API 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 Changed 9 years ago by anonymous

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

comment:2 Changed 9 years ago by mgood

  • Resolution set to worksforme
  • Status changed from new to closed

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

Add Comment

Modify Ticket

Change Properties
<Author field>
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.

E-mail address and user name can be saved in the Preferences.

Note: See TracTickets for help on using tickets.