Thread

I am using the query module and grabbing a mysql datetime.  I am trying to display the localized date using

localized date : {mydate format="%n-%j-%Y"}
localized time : {mydate format="%g:%i %a"}

EE is returning a date and time that is totally wrong and is the same for any datetime entry in the db.  The date returned is always 12-31-1969 and the time returned is always 8:33 pm.  When I format the date and time using sql DATE_FORMAT, I get the gmt values from the db. 

My EE version is v2.2.1 build date 20110705.

Hi SweetEE,

I’m not able to replicate this issue in my installation, also running EE 2.2.1 build 20110705. Could you try this in a template and let me know what it reads? This is exactly what I used to test:

{exp:query sql="SELECT entry_date FROM exp_channel_titles ORDER BY entry_date DESC LIMIT 5"}

localized date : {entry_date format="%n-%j-%Y"}

localized time : {entry_date format="%g:%i %a"} 
<hr>

{/exp:query}

Hi Kevin,
Thanks for replying.  I see what my problem is.  I am using a table I created and I have been storing values in the mysql db as datetime.  I noticed that EE is using an int(10) for storing the date in the table exp_channel_titles.  Is EE using the php gmmktime function with the EE code $this->EE->localize->now;? 

I can easily switch my code to use $this->EE->localize->now since it defaults to the server time if the user is not logged in.  I will switch over my column to be int(10) as the datatype in the database as well.
Thanks for your help,
Dave

Hi Dave,

That’s correct, EE stores UNIX timestamp format throughout. An empty or zero value will come back as around 1970, or a bit before or after depending on your timezone. Glad you found the culprit!