#917 mysql player error in timezone handling,

nikhilgupta10 imported these comments from Sourceforge: "dchassin":My guess is that MySQL and GridLAB-D may not be using same timezone for the internal representation of timestamps. As a result queries and results sometimes convert time inconsistently. In this case it seems that the timezone offset is causing the return value for the next event time to \go backwards.

A temporary workaround might be to do everything with \clock { timezone UTC0; }. But this can be a very big headache and might not always work because other tools may be using machine locale to handle DST and TZOFFSET.

I think the long term fix is add a imezone\ specification to the database connection object and use that to determine how to convert time betwee MySQL timestamps and GridLAB-D timestamps.,

"dchassin":- status: new --> accepted

Component: core --> mysql ,

"dchassin":- summary: mysql player---error in timezone --> mysql player error in timezone handling
Description has changed:

Diff:


--- old
+++ new
@@ -1,14 +1,14 @@
 I am able to read from ~1000 tables as players in my model. One problem that I have is that the start time should be exactly the same as my start time of data that I have in mysql tables. 
 data in all of these tables starts from 08-01-2013 00:00:00, my simulation can be run perfectly when I starts from 08-01-2013 00:00:00 or before this time. But when I change the start time in my simulation (for example 08-01-2013 01:00:00), it stops and I get this error:
+
+~~~~~
 ERROR [2013-08-01 01:00:00 EDT] : model commit failed
 ERROR [2013-08-01 01:00:00 EDT] : exec halted: commit failure
 FATAL [2013-08-01 01:00:00 EDT] : shutdown after simulation stopped premature
 ly
 FATAL [2013-08-01 01:00:00 EDT] : environment startup failed: No such file or
 directory
-
-
-David as you remember when you look at the problem, you find out the problem is with timezone and some how the mysql player can't find the timezone. 
+~~~~~

 Thanks,

"dchassin":- Description has changed:

Diff:


--- old
+++ new
@@ -4,10 +4,8 @@

ERROR [2013-08-01 01:00:00 EDT] : model commit failed ERROR [2013-08-01 01:00:00 EDT] : exec halted: commit failure -FATAL [2013-08-01 01:00:00 EDT] : shutdown after simulation stopped premature -ly -FATAL [2013-08-01 01:00:00 EDT] : environment startup failed: No such file or -directory +FATAL [2013-08-01 01:00:00 EDT] : shutdown after simulation stopped prematurely +FATAL [2013-08-01 01:00:00 EDT] : environment startup failed: No such file or directory

"dchassin":Opened ticket/917 at r4988.,

"dchassin":Posted an attempted fix in r4991. Please try this and let me know whether it works. Here's how it works:

You now can specify the _tzoffset and dst used by the database separately from gridlabd by including the following properties in the database object.

object database {
  tz_offset -5h; // db timestamps trail UTC by 5 hours (i.e., EST+5)
  uses_dst TRUE; // db uses local DST rule (i.e., EST+5EDT)
  // etc...
}

In theory every timestamp written to the database uses the following formula

ts + tz_offset + ( uses_dst && is_dst(ts) ? 3600 : 0);

and reading uses

ts - tz_offset - ( uses_dst && get_is_dst(ts) ? 3600 : 0);
~~~~~,

 "dchassin":By the way, the fix posted in r4991 should also fix ticket/918 correctly.,

 "dchassin":An updated fix in posted in r5020.  The tz_offset variable is an int32 and cannot have units associated with it.,

 "dchassin":- **Resolution**: none --> validate
,

 "dchassin":- **Milestone**: Unscheduled --> Version 3.2 RC1
,

 "dchassin":Fix incorporated into 3.2.,

 "dchassin":- **Milestone**: Version 3.2 RC1 --> Knothole Interim
,

 "dchassin":Fix needs to be integrated into trunk before knothole.,

 "dchassin":- **status**: accepted --> assigned
,

 "andyfisher":Can this be closed after integration?,

 "dchassin":Yes if confirmed fixed  ,

 "dchassin":- **assigned_to**: David P. Chassin --> Andy Fisher
,

nikhilgupta10 / GridLAB-D

#917 mysql player error in timezone handling, #2643