Mudlet 2.1 Database Problem

Jor'Mox
Posts: 1142
Joined: Wed Apr 03, 2013 2:19 am

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

The Timestamp as initially stored when the record is created is readable. The problem is that you cannot update the record, or update the Timestamp. Also, the Timestamp is stored in a special table which provides its own function to translate the time from the epoch value that is stored into a string. That would be where an option for displaying local time would be ideal, I would think, instead of making it a multistep process.

Specifically, this happens. You create a new record. You retrieve that record using a fetch command, and all the data is there as expected. You then try to change any field, and update the record, and an error is generated because the Timestamp format is not recognized. Then even when that problem is overcome, you have no way of automatically generating a new Timestamp for the record if desired, which leads to problems, since the most obvious solution is to replace the epoch value currently stored with one generated by your computer, which will be in local time. So if the record is retrieved again, and the change was made shortly after the initial record was created, it will actually show the record as having a Timestamp from before it was created.

Jor'Mox
Posts: 1142
Joined: Wed Apr 03, 2013 2:19 am

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

Looking at the documentation you pointed me to, it seems that SQLite stores timestamps as a string, using GMT. When they are read in, they are transformed into epoch values by something, but that part seems to be working fine. So I think the idea solution would be transform the epoch values back into strings so that the data is stored in a consistent manner.

And then, modify the db.__Timestamp:as_string function to allow for the value generated to be in local time (at least optionally, if not by default). Similarly, there should be a reasonably accessible method for setting a timestamp to a particular time, or at least refreshing the timestamp (i.e. changing the timestamp to match the time of when the record was updated) and having that automatically converted to the correct epoch value for storage in the database using a GMT value (even though the time give will often be in local time).

This is the relevant function as I'm seeing it (there are similar functions for as_table and as_number, which would also need to be addressed):
Code: [show] | [select all] lua
function db.__Timestamp:as_string(format)
   if not format then
      format = "%m-%d-%Y %H:%M:%S"
   end

   return os.date(format, self._timestamp)
end
If we changed it like this:
Code: [show] | [select all] lua
function db.__Timestamp:as_string(format, local_time)
   if type(format) == "boolean" or not format then
      format = "%m-%d-%Y %H:%M:%S"
   end
   if type(format) == "boolean" then
      local_time = format   -- if just local_time is passed, set value appropriately
   end
   if local_time == nil then
      local_time = true   -- sets display as local time to be the default behavior
   end
   
   local time_dif = os.date("*t").hour - os.date("!*t").hour   -- calculates time difference between local and GMT
   local cur_time = os.date(format, self._timestamp)  -- converts epoch to time as specified by format
   if local_time then
      cur_time.hour = cur_time.hour + time_diff   -- applies time difference to show local time
   end
   return cur_time
end
And here is an example of a possible function to update the timestamp:
Code: [show] | [select all] lua
function db.__Timestamp:refresh(time, format, is_local)   -- accepts time in table, epoch or string format, if as string, formatting must be supplied
   if not time then   -- use current time if no arguments provided
      time = os.date("!*t")
      format = ""
      is_local = false
   end
   if type(format) == "boolean" then   -- if no format provided, use value in format as value for is_local
      is_local = format
      format = ""
   end
   if type(time) == "string" then   -- if time provided is given as a string, convert to table
      time = datetime:parse(time,format)
   end
   if type(time) == "table" then   -- if time is in table format, convert to epoch
      time = os.time(time)
   end
   if is_local then   -- if time provided is local time, convert to GMT
      time = os.time(os.date("!*t",time))
   end
   self._timestamp = time   -- store new timestamp value
end

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

The *t trick won't work - was looking it up yesterday, it does not account for DST changes apparently from people that know: http://lua.2524044.n2.nabble.com/os-tim ... 61931.html (even though it worked for me in my non-DST timezone and for someone who is currently in DST).

I'm thinking of adding a function on the C++ side that would, given a UTC epoch, transform it into a "local time" epoch which would allow further manipulation in Lua.

Didn't get to the updating a record part yet - good point, will look at that as well.

Edit: Hm... I wonder why the timezone calculation wouldn't work actually - maybe it's for 'remote' calculation of time in another zone, and not for your local, current time which is what we are looking at right now. Looking at https://github.com/stevedonovan/Penlight/issues/89, I'll go with how https://github.com/stevedonovan/Penligh ... te.lua#L75 does it right now.

Jor'Mox
Posts: 1142
Joined: Wed Apr 03, 2013 2:19 am

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

So, according to this, you can calculate the time zone offset (in seconds) using this function:
Code: [show] | [select all] lua
local function get_timezone()
  local now = os.time()
  return os.difftime(now, os.time(os.date("!*t", now)))
end
It would obviously need to be applied to the time as an epoch value. A little testing reveals that if you add the value returned by this number to time in GMT, you get local time, which makes for a very simple conversion. No coding in C necessary.

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

Yep, added that in for as_string. As the manual makes no mentions of timezones and the most obvious expectation is your current timezone (combined with the fact that I don't think anyone used this, and it the change does not permanently modify db data, but only reporting).

Going to look into updating/deleting records now.

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »

So far, it looks like an SQLite trigger that runs an update on the timestamp is the best way to go.

Jor'Mox
Posts: 1142
Joined: Wed Apr 03, 2013 2:19 am

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

Just so long as it is also possible to update a record without changing the timestamp, anything that works sounds good to me.

Jor'Mox
Posts: 1142
Joined: Wed Apr 03, 2013 2:19 am

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

Oh, and if you post what you have for the as_string function, I can write equivalent versions for as_table and as_number for you.

User avatar
Vadi
Posts: 5035
Joined: Sat Mar 14, 2009 3:13 pm

Re: Mudlet 2.1 Database Problem

Post by Vadi »


Jor'Mox
Posts: 1142
Joined: Wed Apr 03, 2013 2:19 am

Re: Mudlet 2.1 Database Problem

Post by Jor'Mox »

From looking at what you referenced, it seems that his methodology would work for any time, and properly account for daylight savings time as it applies to the timestamp you are converting. So, would it not be better to use self._timestamp in place of os.time() for ts in this case?

Here is what you have for the three functions in question:
Code: [show] | [select all] lua
function db.__Timestamp:as_string(format)
   if not format then
      format = "%m-%d-%Y %H:%M:%S"
   end

   -- the timestamp is stored in UTC time, so work out the difference in seconds
   -- from local to UTC time. Credit: https://github.com/stevedonovan/Penlight/blob/master/lua/pl/Date.lua#L85
   local ts = os.time()
   local utc = os.date('!*t',ts)
   local lcl = os.date('*t',ts)
   lcl.isdst = false
   local timediff = os.difftime(os.time(lcl), os.time(utc))

   return os.date(format, self._timestamp + timediff)
end

function db.__Timestamp:as_table()
   return os.date("*t", self._timestamp)
end

function db.__Timestamp:as_number()
   return self._timestamp
end
Here are my proposed changes:
Code: [show] | [select all] lua
function db.__Timestamp:as_string(format)
   if not format then
      format = "%m-%d-%Y %H:%M:%S"
   end

   -- the timestamp is stored in UTC time, so work out the difference in seconds
   -- from local to UTC time. Credit: https://github.com/stevedonovan/Penlight/blob/master/lua/pl/Date.lua#L85
   local ts = self._timestamp
   local utc = os.date('!*t',ts)
   local lcl = os.date('*t',ts)
   lcl.isdst = false
   local timediff = os.difftime(os.time(lcl), os.time(utc))

   return os.date(format, self._timestamp + timediff)
end

function db.__Timestamp:as_table()
   -- the timestamp is stored in UTC time, so work out the difference in seconds
   -- from local to UTC time. Credit: https://github.com/stevedonovan/Penlight/blob/master/lua/pl/Date.lua#L85
   local ts = self._timestamp
   local utc = os.date('!*t',ts)
   local lcl = os.date('*t',ts)
   lcl.isdst = false
   local timediff = os.difftime(os.time(lcl), os.time(utc))
   
   return os.date("*t", self._timestamp + timediff)
end

function db.__Timestamp:as_number()
   -- the timestamp is stored in UTC time, so work out the difference in seconds
   -- from local to UTC time. Credit: https://github.com/stevedonovan/Penlight/blob/master/lua/pl/Date.lua#L85
   local ts = self._timestamp
   local utc = os.date('!*t',ts)
   local lcl = os.date('*t',ts)
   lcl.isdst = false
   local timediff = os.difftime(os.time(lcl), os.time(utc))

   return self._timestamp + timediff
end
Note that the as_number and as_table functions just have a direct copy/paste from the as_string function, with the time returned in the appropriate format.

Post Reply