dashCommerce

Having a hard time nailing this one down. My shipping/ paypal providers are all in place and functioning properly, but anyplace that displays the order status is showing the date/time as 5 hours ahead of what it should be. I have checked the times and time zones on my computer and on the server,  they are correct. If I query the tables directly, they are correct. But the time stamp on receipt.aspx, admin/orderedit.aspx, and the e-mail notifaction add 5 hours to the time.

I believe this is coming fromt the order.ToHtml() method, but I don't see anything in the code that should be causing that. Another oddity is that the admin/orders.aspx page shows the correct time. Is there any localization going on with the date/time stamps I should be checking? Anybody else see this problem?

TIA,

Ben 

Hi Ben,

A lot of areas within dashCommerce are using the DateTime.UtcNow() function, this is probably why you are seeing the 5 hour difference.  I'll look at the receipt.aspx form and let you know what I find.

Tim

You can check out in the database the table dashCommerce_Store_Transaction
and on the column TransactionDate change the Default Value ir Binding to (getutcdate())
Let me know if this helped
 

yitzchok:

You can check out in the database the table dashCommerce_Store_Transaction
and on the column TransactionDate change the Default Value ir Binding to (getutcdate())
Let me know if this helped
 

Tried that, no change. Have a look at this:

        select transactiondate, createdon, modifiedon from dashCommerce_Store_Transaction

results in:

            2008-03-25 19:16:25.573    2008-03-25 14:16:25.590    2008-03-25 14:16:25.590 

Notice the TransactionDate column 5 hours ahead? 

All three of these columns have their "Default Value or Binding" set to (getutcdate()) -- TransactionDate column, was, however, set to (getdate()).

Is subsonic doing something to populate the date in this field? 

I'll try to check it out - that really can be SubSonic doing some Magic here:)

SubSonic wouldn't do anything with TransactionDate column but SubSonic might have a different way of putting in createdon, modifiedon.

 Well, I have my solution, but I don't know if this would be considered a fix or a band-aide, so I'll post and ask for feedback from others.

My findings are that all datetime fields in the DB are being populated with localized date/time stamps EXCEPT for the TransactionDate in the dashCommerce_Store_Transaction table, which is being populated as UTC date/time. --As i'm writing this i'm begining to think that we should have some uniformity in the db, no? Anyways, my solution was to localize the datetime as it's coming out of the DB in the order.toHtml() function. Here we go:

Store\Models\Order.cs

Around line 324 (looks like this):

sb.Append(string.Format("<tr><td colspan=\"2\">{0}&nbsp;{1}&nbsp;{2}</td></tr>", LocalizationUtility.GetStoreString("OrderDate"), this.TransactionCollection[0].TransactionDate.ToLongDateString(), this.TransactionCollection[0].TransactionDate.ToLongTimeString()));

Delete this line and replace with this:

         //Create a temporay datetime and localize it since the DB field is UTC
        DateTime orderDate = this.TransactionCollection[0].TransactionDate.ToLocalTime();
        sb.Append(string.Format("<tr><td colspan=\"2\">{0}&nbsp;{1}&nbsp;{2}</td></tr>", LocalizationUtility.GetStoreString("OrderDate"), orderDate.ToLongDateString(), orderDate.ToLongTimeString()));
        sb.Append("<tr><td colspan=\"2\">&nbsp;</td></tr>");

Hi Ben,

All of the date fields should be populated by the getutcdate() function in SQL Server - the TransactionDate may be the outlier - are you finding something different? I'm not clear on what you have above - the localized time statements concern me a bit so I want to be clear. The app should be putting all times in  UTC.

Chris

Here is what is happening (I think).

The application is actually putting in Local Times in to the database (except for TransactionDate that is going in as UTC because that was manually set in code).

Now here is the reason.

SubSonic wont use the default's from the Database except if you set setPropertyDefaultsFromDatabase="true" in the SubSonic Section on the provider leavel in the web.config. But that will cause SubSonic to Connect to the Database a lot more times when creating new objects to check for defaults (SubSonic.RecordBase<T>.SetDefaults()) (and SubSonic keeps the Default values in the generated objects so if you change it in the DB you have to Regenerate the code)

SubSonic 2.0.3 Always uses DateTime.Now  (SubSonic.Utilities.Utility.GetDefaultSetting) (CreatedOn, ModifiedOn)
But there is a solution from the latest version (2.1) of SubSonic to getting UTC that is to use useUtc="True".

(thats also why Guid's has to be manually set)

Now what is the solution?

• setPropertyDefaultsFromDatabase? - (I don't think so)
• Change this in the SubSonic Source V2.0.3? (Maybe)
  
• Update to version of SubSonic 2.1 (That is in Bata 2 - Chris wont like this :)  (but you almost don't have to change anything))
• Any other way?
  

Chris, i'm finding the same as yitzchok posted above, times are going into the db as localized, not UTC (except for the TransactionDate). As far as a solution, i'm afraid I can't be of much help there as I have zero experience with SubSonic (never even heard of it before this project), although it seems like something I need to look into instead of writing my DALs from sctratch! My "band-aid" fix is working for my needs for now, but I wouldn't use that as a final solution.

Looking further ahead along this issue, if the end goal is to have the times in db in UTC (which is a good idea, IMHO), I also think there should be some form of localization going on when displaying to the end user. Either based on server time zone (quick and dirty), or user's profile options (more elegant, more work). Thoughts? I can probably help out with the profile localization coding if you'd like.