Thread

I’m having a hard time searching this subject in the forums and in the docs. Can someone explain the difference between and entry’s entry_date, and the date values stored in the year / month / day columns in that table? How is year month day calculated before they are stored in those fields?

I have custom queries that are checking for year/month in the URL, and it would be easier for me to match against the year / month columns rather than to mess with DST and the entry_date - which I’m having enormous trouble with even after reading (or maybe because of reading) this Localization page. The first sentence reads “ExpressionEngine stores all dates and times in UTC (Universal Coordinated Time)...” but it looks to me like the entry_date and edit_date of a channel entry are stored in different formats.

I’m also experimenting with the channel entries tag and the year=”” month=”“ and start_on=”” stop_before=”“ parameters and am not clear how those differ. Do the year/month parameters look at the year and month columns? Or do they decode the entry_date? Same question for start_on and stop_before; I’m assuming these look at the entry date. Does DST affect these?

Are the year / month / day columns ever used for anything?

Ryan.

Aside to explanations to all your questions can you give us some context to your issue that we could maybe try and solve for you to or suggest an approach?

The first sentence reads “ExpressionEngine stores all dates and times in UTC (Universal Coordinated Time)...” but it looks to me like the entry_date and edit_date of a channel entry are stored in different formats.

The entry_date along with other dates are specifically stored in Unix time. The entry_Date is in another format. I will ask internally on that one for you

I’m also experimenting with the channel entries tag and the year=”” month=”“ and start_on=”” stop_before=”“ parameters and am not clear how those differ.

Using start_on= and stop_before= you are limiting a query by date range
Using the year=, month= and day= you are limiting the query by a date

Do the year/month parameters look at the year and month columns? Or do they decode the entry_date?

They decode the entry date

$this->EE->localize->set_gmt(mktime(0, 0, 0, $smonth, $sday, $year));

Same question for start_on and stop_before; I’m assuming these look at the entry date. Does DST affect these?

Yes entry_date again and also is run through localize

$sql .= "AND t.entry_date >= '".$this->EE->localize->convert_human_date_to_gmt($this->EE->TMPL->fetch_param('start_on'))."' ";

Are the year / month / day columns ever used for anything?

The year/month/day columns are used specifically by the display_by= parameter

I hope the above helps you initially but as I say if you share what exactly you are tryign to achieve we can try and help in that regard.

John - Thanks for the response.

All I want to (consistently) do is make sure that any custom queries I need to write would return the exact same entries as if I were using the channel:entries tag. I’m doing reports that involve money calculations based on year/month in the URL. I need to use custom queries so that I can do things in the query like SUM, COUNT, and GROUP BY, among other things like extra JOINs, etc.

I had all this working perfectly in the EE1 version of the site - every custom query matched similar queries done with weblog:entries, which also matched the dates shown in the control panel. This is important when I’m dealing with reports that show amounts of money that are owed, and other money-related things.

I have found the first problem. It appears that all the entry_dates in exp_channel_titles table have been adjusted to 10800 seconds (3 hours) less than the same field in the EE1 version of the site (see attached pic). That was a curveball I wasn’t expecting. I think I need to solve that first before knowing if the reporting templates are “broken” as I thought (old monthly reports showing numbers that don’t match same report from live site). This is likely because some entries that were close to midnight, now that they are 3 hours off, are getting converted to the “wrong” day - or the day that doesn’t match what was recorded in the EE1 version of the site.

I may be able to adjust some of these entries to fix those edge cases, but I’m wondering about the 3 hour shift - is this a known issue in EE site upgrades? Wondering if I need to run a script that pulls the old entry_id data from original DB and update these entires in the new DB to match old value.

I ran this query:

UPDATE exp_channel_titles 
SET entry_date = entry_date + 10800

This set every entry_date back to exactly what it was in the EE1 DB which…didn’t seem to help anything. Now everything is 4 hours off.

I’m comparing this to a local EE1 version of the site - the entry dates are all the same now, but my reporting page that contains the custom queries shows different results. For example (using fake numbers…), a report from November 2010 should say 40 orders, 80 items - but the same report in the EE2 version of the site says 41 orders, 82 items.

Anyway, that entry_date adjustment didn’t seem to help matters at all. Not sure I want to adjust them back, because I feel like the values in that column shouldn’t have changed at all during the upgrade process.

In one other experiment, I adjusted the server_offset in my config file to adjust for the 4 hour difference between entries in EE1 and EE2:

$config['server_offset'] = '-240';

This made the entries in my “Orders” channel appear to have the exact same dates as the Orders in EE1, when viewing them in the control panel. However, back on the template, I was still seeing discrepancies in some of the month reports (orders and purchased items being off by 1 or 2, which affects the order totals, etc).

EE1 site: 1.6.9 - Build 20100805
EE2 site: 2.1.4b - Build 20110201

Ryan,

Did you have a server offset in there to begin with? There is a bug (fixed for next release) where multiple date headings could be created for entries with very similar dates. Perhaps you’re running into this as well?

Ryan, did you find a solution? I’m currently bitten by date-quirks as well.

Try the latest beta 2.1.4 (20110411) and also i found DST causes more headaches
than necessary. i usually set system DST off and user DST off.

Mod Edit: The rest of this post and subsequent related posts were split into their own thread here.

I haven’t found a complete solution, no. Not sure when I’m going to be able to get back into this, even. Very frustrating. I really wish all the original timestamps would have been left alone.

Hey Ryan—

I can see the unix timestamp for each entry shows a difference of 10,800 seconds between the EE1 and EE2 DBs after upgrade. I know you’ve since run a query on your DB to try to fix this difference, but can you confirm that immediately after the upgrade and before you ran the query to add 10,800 seconds, the entry date/times shown in the CP reflected that same 3 hour difference? If it wasn’t the same difference, how much of a difference was it?

If you create a new entry, does it also carry a 3 hour difference with the unix timestamp in the DB? (And by the way, let’s not even pretend like trying to sort out timestamp issues isn’t a mind bending experience. I have to use a tool like an online Epoch Converter to verify timestamps independent of the software that’s supposed to be interpreting them.)

Also, I’m assuming but just want to confirm: the upgrade was from EE1 v1.6.9 - Build 20100805 to EE2 v2.1.4b - Build 20110201, correct?

Not that it seems to come into play here, but the edit_date column for channel entries isn’t a unix timestamp. After parsing it out, it appears to be comprised like so, based on the local time of the user who made the edit:

for example: 20110504102909
2011 - year
05 - month
04 - day
10 - hour
29 - minute
09 - second

You’re right that the timestamps for entries in EE1 and EE2 should be the same since both versions stored timestamps based on UTC, however using a server offset—-if the server’s timezone hasn’t changed and you didn’t use an offset in EE1—-shouldn’t be necessary.

If you don’t use an offset, do the timestamps in your DBs and the date/times in your CPs match when comparing EE1 and EE2? If not, where are the differences and by how much are they off? (I know they don’t match, but I’m looking for specifics and trying to drill down to the problem area.)

Coming at things from a second angle, is it possible for you to post the code you’re using on your reporting page? You don’t necessarily need to post the whole thing, just one of the problematic portions that shows a DB query and the code used to display the results.

Thanks for your patience while we try to figure out what’s going on here, Ryan.

Kevin

Hi Ryan, we’ve discussed this quite a bit internally, and in some cases can replicate a time shift after upgrading from 1.x to 2.x. However, that shift is variable due to on a number of factors, including the way EE 1.x stored dates, and isn’t always fixable in the update script. Let us know how you progress here.

I haven’t had much time to get back to this lately, but am hoping to in the next week. This thread isn’t dead until my report pages work the same in EE2 as they do in EE1.

So far, I’ve determined that the closest I can get my EE2 install to replicate the EE1 install (as far as dates go) is to run the SQL that adds the 10800 seconds back. This way, I know I’m starting from the the exact same number of seconds in both installs, regardless of what EE or the templates tries to do to the data before it renders on the template.

Hey Ryan—

I can see the unix timestamp for each entry shows a difference of 10,800 seconds between the EE1 and EE2 DBs after upgrade. I know you’ve since run a query on your DB to try to fix this difference, but can you confirm that immediately after the upgrade and before you ran the query to add 10,800 seconds, the entry date/times shown in the CP reflected that same 3 hour difference? If it wasn’t the same difference, how much of a difference was it?

We started this upgrade so long ago, that I couldn’t tell you. March 23, 2011, to be exact. I didn’t notice the date issues until I got into converting the PHP on the templates to EE2 syntax in late April.

If you create a new entry, does it also carry a 3 hour difference with the unix timestamp in the DB?

Also, I’m assuming but just want to confirm: the upgrade was from EE1 v1.6.9 - Build 20100805 to EE2 v2.1.4b - Build 20110201, correct?

I’ll have to look into the creation of new entries. I’m more concerned with older existing entries - I don’t want older financial-related data to be off in any way from what was previously reported. You are correct about the versions and build numbers.

Not that it seems to come into play here, but the edit_date column for channel entries isn’t a unix timestamp. After parsing it out, it appears to be comprised like so, based on the local time of the user who made the edit:

for example: 20110504102909
2011 - year
05 - month
04 - day
10 - hour
29 - minute
09 - second

Right. John Henry mentioned earlier in this thread: “The entry_date along with other dates are specifically stored in Unix time. The entry_Date is in another format. I will ask internally on that one for you” The edit date and entry date are definitely in different formats. Edit date doesn’t matter to me so much, but I’m just curious why everything isn’t just stored as a UNIX timestamp.

Also, I’m not sure it’s been clarified: do the values of the day/month/year columns reflect an “easy to read” version of the UTC/GMT time, or has localization voodoo been performed before putting those numbers in the DB?

You’re right that the timestamps for entries in EE1 and EE2 should be the same since both versions stored timestamps based on UTC, however using a server offset—-if the server’s timezone hasn’t changed and you didn’t use an offset in EE1—-shouldn’t be necessary.

I agree - I’ve since found that using the offset is not what I need to do. I think that without an offset, and running my now infamous +10800 query, that things are as close as they’ve been - and that is to say off by an hour. I haven’t looked at this issue since April 26, so I have to refresh my memory.

Hi Ryan,

Right. John Henry mentioned earlier in this thread: “The entry_date along with other dates are specifically stored in Unix time. The entry_Date is in another format. I will ask internally on that one for you” The edit date and entry date are definitely in different formats. Edit date doesn’t matter to me so much, but I’m just curious why everything isn’t just stored as a UNIX timestamp.

Also, I’m not sure it’s been clarified: do the values of the day/month/year columns reflect an “easy to read” version of the UTC/GMT time, or has localization voodoo been performed before putting those numbers in the DB?

I’ll try and get some clarification on these questions for you.

Cheers

Greg

OK, I’m sort of giving up here. This is so baffling - I have spent numerous hours on something that I don’t think should be an issue. Why the timestamps were touched at all is beyond me - a UTC time is a UTC time, no?

I’ve decided to just go back into old entries and modify the entry_dates of any necessary entries to make my reports match what exists on the live site. I accept that every other date will be different- and I just don’t care any longer.

Ryan, I’m sorry we weren’t able to get you a definitive resolution on this problem. I did log an internal bug about 1.x installs going ahead by an hour during the conversion process. That’s where it goes off at. I created a brand new install with just a single entry (the one that EE creates during install) and immediately converted to 2.1.5 beta. The entry wasn’t visible since the entry is now in the future.

So this is a slightly different issue than what you’re dealing with, but it stems from the same issue.

I’m hopeful we can re-assess the issue before 2.2 comes out, but I can’t guarantee it will get fixed. Thanks in advance for your patience.

I’m going to go ahead and close this for the moment, but we can reopen at a later time if needed.