Convert date and time string into Raw / Epoch time

I am trying to find a way to get MP3Tag to convert a date/time text string (of the format: YYYY-MM-DD HH:MM:SS) into the Unix Epoch time format that MP3Tag uses for its 'raw' dates.

As a very inexperienced user, I am pleased that I managed to entirely create my own solution for converting MP3Tag's raw epoch time format into 18-digit LDAP / Win32 FILETIME (which is used by Foobar2000's music library). I searched for absolutely ages to find a formula for how to convert between the two (almost all of the search results were for programming code and didn't explain the actual calculation needed).

Eventually I found a calculation, and I constructed this in MP3Tag:

$add($mul(%_file_mod_datetime_raw%,10000000),116444736000000000)

It works great, and it means that I can set my music library's 'First Added' date based on the timestamp of the file (instead of whatever arbitrary date the database just so happened to encounter the track for the first time). Also, the dates don't all get lost if you restore the database from a backup (you can just re-import them from the timestamps in the 'First Added' tags).

However, this is still a bit limiting because the timestamp on the file doesn't always correspond to when I added it to my music library, therefore I'd like to be able to manually type a date string into a tag, and have that converted to the relevant format tag for my music library's database.

Seeing as I've already worked out how to get from MP3Tag's raw Unix Epoch time to LDAP / Win32 Filetime, I can use the exact same method for my own dates. The issue that I am stuck on though is how to calculate Unix Epoch time for a specified date (which is needed as part of my calculation).

Searching for a calculation for this was even worse than the Win32 < > Epoch conversion, but after a couple of hours I think I have found one.

What I need help with is how to strip all non-numeric characters from the YYYY-MM-DD HH:MM:SS date string (dashes, space, and colons) and then use the component parts of the resulting numeric sting (year, month, day etc.) in the calculation below. The output of this calculation would then take the place of %_file_mod_datetime_raw% used in the action that I create above.

Of course I only need to do this assuming that MP3Tag doesn't have an existing way to display a user-specified date as 'Raw'. But the only options that I have seen are for 'created' and 'modified' file dates to be displayed as raw, not user-specified dates.

tm_sec + tm_min*60 + tm_hour*3600 + tm_yday*86400 +
    (tm_year-70)*31536000 + ((tm_year-69)/4)*86400 -
    ((tm_year-1)/100)*86400 + ((tm_year+299)/400)*86400

tm_sec = seconds
tm_min = minutes
tm_hour = hours
tm_yday = days since January 1 of the year ()
tm_year = calendar year minus 1900

Source: c++ - Math to convert seconds since 1970 into date and vice versa - Stack Overflow

The idea is to end up with a raw numeric representation of the specified date/time which I can then plug-in to my formula to convert it into LDAP / Win32 file time that my music library recognizes.

Any help would be greatly appreciated.

Thanks.

Do I understand your question correct: You want to "clean/reduce" a string from
2022-12-26 11:23:30
to
20221226112330

One possible solution is to use
$replace(%FULLDATETIMESTRING%,-,,:,, ,)
This command replace every dash with nothing, every colon with nothing and every space with nothing.
Please use your matching, existing tag name instead of FULLDATETIMESTRING.

Yes, you are correct LyricsLover, but that is only the first part of what I need to do.

The second part is to reconstruct the calculation (that I stated above) by using parts of the cleaned/reduced string.

So, for your example of 20221226112330, instead of

tm_sec + tm_min*60 + tm_hour*3600 + tm_yday*86400 + (tm_year-70)*31536000 + ... etc.

it should be:

30 + (23 x 60) + (11 x 3600) + (360 x 86400) + ((2022 - 1900) - 70) x 31536000 + ... etc

Note: today is the 360th day of the year, but I don't know how to obtain the day-number of the year from digits 4 through 8 in the string (1226, i.e. Dec 26).

Also, the tm_year value has to be calculated (YYYY-1900) so the first 4 digits of the clean string (in this case 2022) needs to have 1900 subtracted from it and then substituted in the 4 places where 'tm_year' appears in the above calculation.

So to clarify, what I am trying to get is the code for an MP3Tag Action that when given an input of 2022-12-26 11:23:30 it gives an output of: 1672053810

If the MP3Tag Action was given an input of 2023-05-31 19:08:14 then the output should be: 1685560094

and so on.

1 Like

I'm not sure how you can achieve this with the available possibilities.
Theoretically, you could fill the parts from the date/time-string into user defined tags like

tm_sec
tm_min
tm_hour
tm_yday
tm_year

But this still not solves the calculation for the 360th day-of-the-year in your example (the 1226, i.e. Dec 26).
The subtraction of 1900 could be achieved with $sub(%tm_year%,1900).

Here you can find various examples to calculate it, unfortunately none of them is easy to adopt with the known Mp3tag functions AFAIK (please scroll down to "Convert from human-readable date to epoch" for the various program lanuage calculation examples).

Thank you, but I have already seen that page (and countless others like it) as I described in my original post.

There are literally thousands of results about how to achieve this in various programming languages using libraries or built-in variables. But none of them explained the actual mathematical calculation needed (until I eventually found the calculation that I posted above).

Could you please show me how I would break the clean date string into its component parts?

Presumably, if I used tags with the same element names as in the calculation formula (tm_sec tm_min tm_hour tm_yday tm_year) then I could just use the original formula as it is (with % signs wrapped around the elements). Obviously I would adapt the formula to use MP3Tag's particular way of doing math ($add, $mul etc.) which I have already had some success with in my earlier Epoch > LADAP / Win32 conversion Action that I created. I can then add a step at the end of the action sequence to delete the placeholder tags for the individual element values that were used in the calculation.

If I can then find a formula for the day-number of the year, that will be the final piece of the puzzle (which I should be able to construct for myself by copying any example you give here on how to split the number and insert the correct parts into the appropriate section of the formula).

I'm sure there is a day-of-the-year formula out there somewhere, it's just that almost all of the search results I get are the same questions over and over again with the same code-based programming language answers with no explanation of how to do the actual calculation. I'm pretty sure I can find it if I search long enough, so I'd like to get the rest of the calculation ready to so that I can drop in that final part.

Many thanks.


P.S. This would be so much easier if MP3Tag's raw date display worked for any date. I would not then need to do any calculation at all to obtain epoch time.

You don't have to clean it first for this purpose. You can just break it into parts by the separators (dash, space and colons).

You could use an action with type "Guess values":

Guessing pattern for YYYY-MM-DD HH:MM:SS
%tm_year%-%tm_month%-%tm_day% %tm_hour%:%tm_min%:%tm_sec%

Please press ALT + T to see the automatically created new custom tags and their content.
Example (tags displayed sorted alphabetically!)
image

Please adjust your source field to the name where you currently have your YYYY-MM-DD HH:MM:SS saved. The above example is using %ALBUM% only for better understanding.

I don't have any brilliant formula for getting day-of-the-year value, but there is a clumsy but sure-fire way to achieve it.

  1. Define number of days of each month in separate temporary tags
  2. Add them up one by one until number of month (after taking care of leap year)
  3. Finally add tm_day-1

But sincerely I suppose you will have much better luck to do everything using other tools (say, a python script, as you seems to have some programming background). That could be more manageable and simpler. Mp3tag simply isn't designed for such arithmatics and data conversion.