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"}

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

enter image description here

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...

2 Answers 2


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

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.

  • Hi - Thanks for your response! have clarified reason for asking. My outputs for edit_date (inside queries or channel:entries) do not format as expected. Jan 12, 2013 at 17:01
  • 1
    I guess there was something wrong with my install - I re-uploaded all files and all was well and good, edit_date format="" now works as expected. Thanks for the information above, it helped me realize this wasn't a bug. Jan 12, 2013 at 18:19
  • 1
    No problem. Glad things worked. Jan 12, 2013 at 18:25
  • 1
    Maybe I was too quick to comment - It seems {edit_date format=""} doesn't work inside a {exp:query sql=""}{/exp:query} tag pair - at least not in any versions of EE I have. Jan 12, 2013 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.