Now that I think about this, is this a 'feature' of revExecuteSQL ? A string that looks like a time gets coerced by the command into a time and adjusted for time zone without regard to the field type of the destination database field. Can that be what's happening?
On Tue, Mar 24, 2015 at 9:48 PM, Paul Foraker <p...@whitefeather.com> wrote: > In a table in a MySQL database, I need a field displaying a time period > corresponding to the half hour period during which a record was posted. The > period is in the form "HH:MM-HH:MM AM|PM". I have set the field type to > VARCHAR(10) and LiveCode is passing the string to the query handler. MySQL > is interpreting the incoming text as a date and converting it to my local > offset from server time. > > In the Variable Watcher, before the revExecuteSQL, the pPeriod has the > correct time period; e.g., 11:00-11:30 PM. In the database, Sequel Pro > shows the period field with a value of 6:00-6:30 PM when Jacque in > Minnesota posts the record at 11:00 PM Minnesota time, and 4:00-4:30 PM > when I do it in California. > > I'm surprised that MySQL is doing this interpretation of a character > string to a time value. How do I prevent that from happening? > > In Sequel Pro, I entered this command: > > INSERT INTO report > (`shortdate`,`period`,`dsp_id`, `dsp_name`, `client_id`,`client_name`, > `behavior_label`, `behavior_desc`, `observed`) > VALUES ("3/15/2014","12:30-1:00 > PM",22,"fred",22,"lucy","jumping","high",1) > > and the period field correctly displayed "12:30-1:00 PM". > > Here's the LiveCode handler: > > on sendReport > pShortDate,pPeriod,pDSPid,pDSPname,pClientID,pClientName,pRows > getConnected ## establishes the database connection > set the itemDel to tab > put "INSERT INTO report (`shortdate`,`period`,`dsp_id`, `dsp_name`, > `client_id`,`client_name`, `behavior_label`, `behavior_desc`, `observed`)" > && \ > "VALUES " into tSQL > repeat for each line thisRow in pRows > put "('" & pShortDate & "', '" & pPeriod & "', '" & pDSPid & "', '" & > pDSPname &"', '" & pClientID &"', '" & pClientName &"', '" & \ > item 1 of thisRow & "', '" & item 2 of thisRow & "', '" & item 3 > of thisRow & "')," after tSQL > end repeat > put ";" into last char of tSQL > revExecuteSQL gBTdbID,tSQL > ... > > Any ideas how to do this? > -- White Feather Software www.whitefeather.com Cell: 408-391-1480 _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode