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.
Mudlet 2.1 Database Problem
Re: Mudlet 2.1 Database Problem
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):
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):
If we changed it like this:
And here is an example of a possible function to update the timestamp:
Re: Mudlet 2.1 Database Problem
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.
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.
Re: Mudlet 2.1 Database Problem
So, according to this, you can calculate the time zone offset (in seconds) using this function:
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.
Re: Mudlet 2.1 Database Problem
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.
Going to look into updating/deleting records now.
Re: Mudlet 2.1 Database Problem
So far, it looks like an SQLite trigger that runs an update on the timestamp is the best way to go.
Re: Mudlet 2.1 Database Problem
Just so long as it is also possible to update a record without changing the timestamp, anything that works sounds good to me.
Re: Mudlet 2.1 Database Problem
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.
Re: Mudlet 2.1 Database Problem
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:
Here is what you have for the three functions in question:
Here are my proposed changes:
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.