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

[anonymous]

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

[anonymous]

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

[Matthew Good]

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