ExpressionEngine edit_date formatting

Question

Is there a reason the edit_date in exp_channel_titles isn't in Unix time format?

I need to display the last edit date of a section of a website so I'm doing a simple query

{exp:query sql="SELECT title, edit_date FROM exp_channel_titles
                 INNER JOIN exp_structure using(entry_id)
                 WHERE (exp_structure.channel_id ='4' OR parent_id ='13') 
                 ORDER BY edit_date DESC LIMIT 1"}
       {edit_date format="%d%S %F %Y"}
{/exp:query}

However the edit date seems to be formatted in a non unix timestamp. Is there a specific reason for this?

/system/expressionengine/libraries/api/Api_channel_entries.php is set to

'edit_date' => $this->EE->localize->decode_date('%Y%m%d%H%i%s', $this->EE->localize->now, FALSE),

Should this be

'edit_date' => $this->EE->localize->decode_date('%U', $this->EE->localize->now, FALSE),

?

Using EE 2.5.5

EDIT - reason for asking

I ask because my outputs do not format correctly:

Eg; edit_Date of 20130111181835

Last Updated - {edit_date format="%F %d, %Y"}

outputs -

Last Updated - October 30, 639867

I've since tried priting the edit date of a normal post and it still it not formatted correctly. I will go check my settings...

Answer 1

ExpressionEngine gives edit_date special treatment inside the Channel Entries loop to account for the way it's saved, but inside a query it's just another 14-digit number. To get it to format properly in a query loop, the SELECT statement can be modified to offset the value from GMT and convert it to a Unix timestamp. Luckily, EE's current_time variable parses early enough to use it as the offset value.

SELECT UNIX_TIMESTAMP(CONVERT_TZ(edit_date,'+00:00','{current_time format="%P"}')) AS edit_date

Answer 2

I'm not certain if your question is answerable as is, at least from non-Ellis Lab developers. It inquires about a reason, rather than asking for guidance or a solution.

What I can say is the edit_date format has been in non-Unix time format from before ExpressionEngine 2.5.5. I checked three sites: one using 2.5.5, one using 2.5.3, and one using 1.7.2 all have the edit date stored the same way.

I can also say, this doesn't change the expected behavior of the edit_date variable as indicated by the ExpressionEngine User Guide

I tested with

{edit_date format="%F %d, %Y"}

and it returns

November 29, 2012

So while I can't state the reason why they would store it in non-unix format, it does look like it was planned, since their date variables work appropriately for it.