Date storage changes for MM 4.0

This forum is for questions / discussions regarding development of addons / tweaks for MediaMonkey.

Moderator: Gurus

jiri
Posts: 5390
Joined: Tue Aug 14, 2001 7:00 pm
Location: Czech Republic
Contact:

Date storage changes for MM 4.0

Post by jiri » Fri Jul 01, 2011 3:45 am

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
Posts: 820
Joined: Sun Sep 21, 2008 9:35 pm
Location: Cupertino, California

Re: Date storage changes for MM 4.0

Post by mcow » Fri Jul 01, 2011 3:13 pm

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
Posts: 3084
Joined: Thu Jun 08, 2006 7:40 pm

Re: Date storage changes for MM 4.0

Post by ZvezdanD » Sat Jul 02, 2011 3:37 am

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.
Magic Nodes 4.3.3 / 5.2 RegExp Find & Replace 4.4.9 / 5.2  Invert Selection/Select None 1.5.1  Export/Create Playlists for Child Nodes 4.1 / 5.4  Expand Child Nodes/Expand All 1.1.2  Event Logger 2.7  Filtered Statistics Report 1.6  Track Redirection & Synchronization 3.4.2  Restore/Synchronize Database 3.1.7 / 4.0  Find Currently Playing Track 1.3.2  Queue List 1.2.1  Add to Library on Play 1.0.1  Tree Report for Child Nodes 1.1.1  Update Location of Files in Database 1.4.3 / 2.2.2  Inherit Child Playlists 1.0.2  Add Currently Playing/Selected Track(s) to Playlist 1.1.2

jiri
Posts: 5390
Joined: Tue Aug 14, 2001 7:00 pm
Location: Czech Republic
Contact:

Re: Date storage changes for MM 4.0

Post by jiri » Thu Jul 07, 2011 3:48 am

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
Posts: 3084
Joined: Thu Jun 08, 2006 7:40 pm

Re: Date storage changes for MM 4.0

Post by ZvezdanD » Thu Jul 07, 2011 8:48 am

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?
Magic Nodes 4.3.3 / 5.2 RegExp Find & Replace 4.4.9 / 5.2  Invert Selection/Select None 1.5.1  Export/Create Playlists for Child Nodes 4.1 / 5.4  Expand Child Nodes/Expand All 1.1.2  Event Logger 2.7  Filtered Statistics Report 1.6  Track Redirection & Synchronization 3.4.2  Restore/Synchronize Database 3.1.7 / 4.0  Find Currently Playing Track 1.3.2  Queue List 1.2.1  Add to Library on Play 1.0.1  Tree Report for Child Nodes 1.1.1  Update Location of Files in Database 1.4.3 / 2.2.2  Inherit Child Playlists 1.0.2  Add Currently Playing/Selected Track(s) to Playlist 1.1.2

jiri
Posts: 5390
Joined: Tue Aug 14, 2001 7:00 pm
Location: Czech Republic
Contact:

Re: Date storage changes for MM 4.0

Post by jiri » Thu Jul 07, 2011 9:44 am

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
Posts: 3084
Joined: Thu Jun 08, 2006 7:40 pm

Re: Date storage changes for MM 4.0

Post by ZvezdanD » Thu Jul 07, 2011 10:09 am

jiri wrote: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?
Magic Nodes 4.3.3 / 5.2 RegExp Find & Replace 4.4.9 / 5.2  Invert Selection/Select None 1.5.1  Export/Create Playlists for Child Nodes 4.1 / 5.4  Expand Child Nodes/Expand All 1.1.2  Event Logger 2.7  Filtered Statistics Report 1.6  Track Redirection & Synchronization 3.4.2  Restore/Synchronize Database 3.1.7 / 4.0  Find Currently Playing Track 1.3.2  Queue List 1.2.1  Add to Library on Play 1.0.1  Tree Report for Child Nodes 1.1.1  Update Location of Files in Database 1.4.3 / 2.2.2  Inherit Child Playlists 1.0.2  Add Currently Playing/Selected Track(s) to Playlist 1.1.2

ZvezdanD
Posts: 3084
Joined: Thu Jun 08, 2006 7:40 pm

Re: Date storage changes for MM 4.0

Post by ZvezdanD » Fri Jul 08, 2011 7:02 am

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.
Magic Nodes 4.3.3 / 5.2 RegExp Find & Replace 4.4.9 / 5.2  Invert Selection/Select None 1.5.1  Export/Create Playlists for Child Nodes 4.1 / 5.4  Expand Child Nodes/Expand All 1.1.2  Event Logger 2.7  Filtered Statistics Report 1.6  Track Redirection & Synchronization 3.4.2  Restore/Synchronize Database 3.1.7 / 4.0  Find Currently Playing Track 1.3.2  Queue List 1.2.1  Add to Library on Play 1.0.1  Tree Report for Child Nodes 1.1.1  Update Location of Files in Database 1.4.3 / 2.2.2  Inherit Child Playlists 1.0.2  Add Currently Playing/Selected Track(s) to Playlist 1.1.2

jiri
Posts: 5390
Joined: Tue Aug 14, 2001 7:00 pm
Location: Czech Republic
Contact:

Re: Date storage changes for MM 4.0

Post by jiri » Fri Jul 08, 2011 7:55 am

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
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: Date storage changes for MM 4.0

Post by Bex » Sun Aug 19, 2012 3:18 pm

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?
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts

jiri
Posts: 5390
Joined: Tue Aug 14, 2001 7:00 pm
Location: Czech Republic
Contact:

Re: Date storage changes for MM 4.0

Post by jiri » Wed Aug 29, 2012 3:17 pm

UploadTime is, but LastAutoSync is not.

Jiri

Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: Date storage changes for MM 4.0

Post by Bex » Wed Aug 29, 2012 5:34 pm

Thanks Jiri, I actually use both of the fields in my Add/Remove playstat script. So now I know how to "adjust" those dates.
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts

rovingcowboy
Posts: 14153
Joined: Sat Oct 25, 2003 7:57 am
Location: (Texas)
Contact:

Re: Date storage changes for MM 4.0

Post by rovingcowboy » Thu Aug 30, 2012 3:17 am

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? :o
roving cowboy / keith hall. My skins http://www.mediamonkey.com/forum/viewto ... =9&t=16724 for some help check on Monkey's helpful messages at http://www.mediamonkey.com/forum/viewto ... 4008#44008 MY SYSTEMS.1.Jukebox WinXp pro sp 3 version 3.5 gigabyte mb. 281 GHz amd athlon x2 240 built by me.) 2.WinXP pro sp3, vers 2.5.5 and vers 3.5 backup storage, shuttle 32a mb,734 MHz amd athlon put together by me.) 3.Dell demension, winxp pro sp3, mm3.5 spare jukebox.) 4.WinXp pro sp3, vers 3.5, dad's computer bought from computer store. )5. Samsung Galaxy 5 Android 5) 6. Proscan tablet Android 4.3 ) 7. amd a8-5600 apu 3.60ghz mm version 4 windows 7 pro bought from computer store.

Post Reply