Date storage changes for MM 4.0

[jiri]

Prior to MM 4.0 all dates in DB were stored as local time, according to timezone set for the PC. Since this caused some problems, we decided to switch to UTC time instead for MM 4.0. It means that all date values as are stored in Songs and Played tables in UTC, i.e. +/- few hours from the values that were in DB in MM 3.x. Note that it doesn't affect pretty much anything else than DB, since the timezone offset is automatically added by MM, so e.g. if a script author accesses FileModified property of SDBSongData object, there's no difference between MM 3.x and MM 4.x.

One more small change was the addition of UTCOffset field to Played table. It contains the timezone offset from UTC of the value in PlayDate field. It has the same scale as other Date fields, i.e. 1 = day, 1/24 = hour. So, for example if a user is in Paris and it's summer (i.e. +2 hours from UTC), UTCOffset will have value 2/24. Note that this addition to MM.DB can be useful for users who travel a lot, since scripts can access playcount statistics both in the current local time, or in time that was local during playback, whichever is preferable.

Jiri

[mcow]

I assume upgrading won't actually change the existing dates in the DB, and that those dates without UTC-Offset will not be given one. Will that mean the displayed time will be displayed as a zero-offset or will they assume the current system offset?

[ZvezdanD]

I still think that this is really wrong what you did. Who are you fooling around with the claim that "it doesn't affect pretty much anything"? Yes, you are right, the scripts that are using SDBSongData would be unaffected (discussable), but what with all of those existing scripts that are using dates from the database directly? They will work incorrectly since you changed dates in the database, for example many of my scripts are affected. It is not the problem with my scripts, but what with all those affected scripts which are not updated anymore because of the lack of interest from their authors?

You didn't answer me, why you added UTCOffset only to the Played table, but not to all date fields in the Songs table? How can we get the local time of the FileModified, DateAdded or LastTimePlayed? I don't care what is some UTC time when I added or modified file, but I want to know what is the local time. Your explanation with time zones could stand only for dates in Played table, but what with Songs table? What if I add or modify one file in one time zone and another file in another time zone?

Well, I think that the solution is very simple. If you want the compatibility with the existing scripts you should keep the existing format of the existing date fields with the local time, and to add Offset field with which you could calculate UTC time, instead as you did inversely.

[jiri]

Few clarifying remarks:
- UTC time is the standard for date/time storage (e.g. for filesystem timestamps) and we needed to adopt this for couple of reasons.
- The UTCOffset field wasn't introduced for backward compatibility, it's added as a base for possible new scripting features.
- The dates in MM3 and MM4 are interchangeable (given not timezone changes), they can be converted one to another using SystemTimeToTzSpecificLocalTime() and TzSpecificLocalTimeToSystemTime() Windows API functions (that's how the dates are converted internally in MM).

Jiri

[ZvezdanD]

Sorry for bothering you, but you didn't say anything that I already don't know. How do you expect that we use Windows API functions in VBScript? As far as I know, that is not possible. Never mind, don't bother to answer, I already found another way to get the current time zone. However, as I said it is not the problem with me and my scripts - there are many other abandoned scripts that would not function properly.

Why you explaining to me the reason to introduce UTC? I didn't say that you should not do that. I said that your implementation is wrong. For example, we have PlayDate field in the Played table. That field in MM3 contained the local time. In MM4 it contains UTC time and there is the new UTCOffset field with which we could calculate the local time. My main question is: why didn't you keep the local time in the PlayDate field for the backward compatibility and with the new Offset field you could calculate UTC time?

Another question that you didn't answer for the second time: why the Songs table don't contain UTCOffset for each date field? How can we know the local time when some file is added to the library? What if I added one track in one time zone and I am now in another time zone? I cannot use Windows functions for time zones in that case. I could calculate difference between UTC and the local time only for the current date/time, but not for some previous date/time. How could computer know in which time zone I was in some specific moment in the past?

[jiri]

How do you expect that we use Windows API functions in VBScript?

Sure, this isn't possible directly. In case the conversion routines are needed, we can discuss and implement them as MM scripting (COM) functions.

My main question is: why didn't you keep the local time in the PlayDate field for the backward compatibility and with the new Offset field you could calculate UTC time?

Well, we always try to make the best decision and definitely don't cause troubles to our scripters on purpose. There are some drawbacks to your suggestion, for example adding any new field to Songs table increases memory requirements and thus also possibly decreases speed of MM (note that this isn't the case of Played table). There really were reasons why we implemented it the way it is.

why the Songs table don't contain UTCOffset for each date field? How can we know the local time when some file is added to the library?

What you ask for here is a 'nice to have' feature. While I agree that it might be nice to have it, we simply haven't implemented it in MM (partly for reasons given in the paragraph above). So, if you take for granted that we didn't want to introduce Offset for each date field, than there are only two options: to store UTC time and calculate local one, or to store the local one and calculate UTC. And we chose the former option in MM 4.0, since it has some advantages for us.

Jiri

[ZvezdanD]

There really were reasons why we implemented it the way it is.

And we chose the former option in MM 4.0, since it has some advantages for us.

I don't know why I expected another answers at all. Would you be nice to mention which are those reasons and advantages? Are they more important then backward compatibility?

[ZvezdanD]

Just to show you that I am not ranting for ranting sake, here is the list of my scripts that are working incorrectly because of the dates modifications:
- Magic Nodes
- Restore Play History/Date Added/Playlists.

I know for at least two Bex's scripts that are affected:
- PlayHistory & Stats
- Add/Remove PlayStat.

That is 4 affected scripts from just 2 scripters. I bet that among numerous trixmoto's scripts there would be at least two or three affected as well. For example, MonkeyRok has in one place the playing statistics grouped by dates (GROUP BY Cast(Played.PlayDate AS Integer))), so with it the user would get the results for UTC time, not for the local time. I also bet that 99.99% of users want to see dates with the local time, not UTC. Not to mention all other scripts that are also dealing with the dates from database.

[jiri]

I have realized that scripters might like to use local2utc() custom sqlite function, that's already present in MM. For the next build, I have also added utc2local() function, so that both directions in date conversions will be ready for use. I think that it should make all the changes and improvements quite painless.

I don't think I have much to add to other comments, we simply always have to consider all the pros and cons of any planned change, which is exactly what we did here.

Jiri

[Bex]

It exist date columns in other tables than Songs and Played. E.g. UploadTime in DeviceTracks and LastAutoSynch in Devices.

Do anyone know if they also are stored as UTC time?

[jiri]

UploadTime is, but LastAutoSync is not.

Jiri

[Bex]

Thanks Jiri, I actually use both of the fields in my Add/Remove playstat script. So now I know how to "adjust" those dates.

[rovingcowboy]

can one of you make a thread in which you post the names of all the effected scripts that are not being updated?
I would like to know if I'm using any?