Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[Matt]

I'm consolidating our code that makes readable strings from dates.  Currently we use a mixture of Windows, C-runtime, and JRiver code.  I'm switching it all to JRiver code.

This formatting gets exposed in the expression engine.

I'm currently supporting two types of tokens:

Windows style tokens like dddd, MM, etc.:
http://msdn.microsoft.com/en-us/library/windows/desktop/dd318131(v=vs.85).aspx
http://msdn.microsoft.com/en-us/library/windows/desktop/dd317787(v=vs.85).aspx

C style tokens like %A, %s, etc.:
http://www.cplusplus.com/reference/clibrary/ctime/strftime/

There are also special expression shortcuts like Elapsed, Decade, Day, etc.  I'm not sure if these should be integrated into the same thing.

There was discussion about adding extensions like "ordinal day of month (1st, 2nd, etc.)" here:
http://yabb.jriver.com/interact/index.php?topic=70843.msg478085#msg478085

If we add extensions, what's the best style to use?  Any suggestions for extensions and names?

Thanks.

[MrC]

I'm not fully clear on what you're looking for, so here are some quick thoughts, in no particular order.

I'm reading that you're going to support both strftime() and the Windows tokens.

If included, ordinality might as well be also available separately, so that it can be used for any number (and not restricted to days); both numeric (1st, 2nd, ...) and word form (first, second, ...).  (I don't know what the latter means re: non-English languages.  And clearly word-form must have some reasonable limit.)

The extensions sound generally like the reverse: english -> date/time.

It would be nice to be able to get a fully formatted date / time returned, perhaps in ISO 8601 combined date/time format.

There's currently no "seconds" available in FormatDate().

Does ConvertDate() accept seconds?  If so, they should be enter-able as seconds, as opposed to fractions of a minute.

It would be nice to be able to compare two dates, and get back an elapsed in any specified format (as opposed to MC's human readable version, 17.7 days... I don't have a very good feel for .7 days).

This should be easier to do (and it actually doesn't work) - I want to get back 1 minute, as per the above point, in any format I desire (seconds, minutes, etc.):

   FormatDate(Math(ConvertDate(3-2-2012 11:32pm) - ConvertDate(3-2-2012 11:31pm)), Elapsed)

As per the above point, a direct DateCompare() function, that returns back a date/time in the specified format would be great.

There are utilities that allow specifying dates as "Now + 1 hour", "1 day ago", "1 hour from now", "yesterday at noon", "last year", "last tuesday".  If you're interested, I'll provide references.

[leezer3]

TBQH, it'd probably be easier to add ordinal extensions as a translatable string, and work from there.
I'd agree that it'd be nice, but by no means essential to be able to use these for other places and in expressions too, not just dates.

-Leezer-

[Matt]

I think we've got this mostly sorted.

The FormatDate(...) expression function will support three types of tokens:

Windows style tokens like dddd, MM, etc. (case-sensitive):
http://msdn.microsoft.com/en-us/library/windows/desktop/dd318131(v=vs.85).aspx
http://msdn.microsoft.com/en-us/library/windows/desktop/dd317787(v=vs.85).aspx

C style tokens like %A, %s, etc. (case sensitive):
http://www.cplusplus.com/reference/clibrary/ctime/strftime/

JRiver tokens (not case sensitive):
year, month, dayordinal, dayname, day, hour, minute, second, filename, elapsedago, elapsed, datetime, date, time, decade


You can mix and match tokens.  If you need to output a literal that fights with a token, use quotes.

Here's an expression that uses a little of everything to display something like 'Thursday the 5th, Janauary 2012' (requires Media Center 17.0.112 or newer):
FormatDate([Date, 0], dayname "the" dayordinal/, MMMM %Y)

[vagskal]

Would this be the appropriate time to ask for a &datatype=[Date] switch for expression fields as well as support for the standard mp3 ID3v2 tags TYER and TDOR?

leezer3 has a good point and as a user in a non English locale I support his view.

[MrC]

Would this be the appropriate time to ask for a &datatype=[Date] switch for expression fields...

For my curiosity, can you elaborate on this?

[MrC]

This one no workie:

FormatDate(Now(), datetime)

[Matt]

This one no workie:

FormatDate(Now(), datetime)

CopyPasta bug.

It output the date / time, but only ate 4 characters so it then output time right after that.

Fixed tonight.

[MrC]

One more that doesn't work:

   FormatDate(Now(), %%)

   should just return a %, but is returning %Fri.

   Fixed in MC 17.0.114.

I see these are unimplemented:

   %U    Week number with the first Sunday as the first day of week one (00-53)    33
   %W    Week number with the first Monday as the first day of week one (00-53)    34

   Fixed in MC 17.0.115.

   %Z    Timezone name or abbreviation    CDT

[MrC]

Here's a little fun, and a handy FormatDate() keyword reminder/cheat-sheet view.  You'll need at least MC 17.0.113.

1. Create a new view, it doesn't matter where you locate it.  Name it something like FormatDate() Specifiers.
2. [IMPORTANT] Set the Rules for file display... to:
    Modify Results: Limit number to 1
3. You can disable Allow tree selection
4. Add a Category, setting the type to be Expression, and paste the entire expression below into the Expression area.  Be sure no initial or trailing blank lines were entered.
5. OK your way out.

Now, you can browse a hierarchical view in the Tree to see the various FormatDate() categories, and how each specifier (aka keyword, token) will format a given date (included at the top of the expression, which you can change - see note).  You can also use the panes to browse, but that is much slower.

This includes all the new specifiers from this thread, minus those mentioned above that are unsupported or broken.

Note: Change ConvertDate(2012-01-01 13:05:59) to Now() to get output for the current date/time.

See attached image.

Expression updated to include fixes in MC 17.0.115.

Code: [Select]

FormatDate(ConvertDate(2012-01-01 13:05:59),
"Date\Month"\M ["M"];/
"Date\Month"\MM ["MM"];/
"Date\Month"\%m ["%m"];/
"Date\Month"\MMM ["MMM"];/
"Date\Month"\%b ["%b"];/
"Date\Month"\MMMM ["MMMM"];/
"Date\Month"\%B ["%B"];/
"Date\Month"\month ["month"];
"Date\Day"\d ["d"];/
"Date\Day"\dd ["dd"];/
"Date\Day"\%d ["%d"];/
"Date\Day"\ddd ["ddd"];/
"Date\Day"\%a ["%a"];/
"Date\Day"\dddd ["dddd"];/
"Date\Day"\%A ["%A"];/
"Date\Day"\day ["day"];/
"Date\Year"\y ["y"];/
"Date\Year"\yy ["yy"];/
"Date\Year"\%y ["%y"];/
"Date\Year"\yyy ["yyy"];/
"Date\Year"\yyyy ["yyyy"];/
"Date\Year"\%Y ["%Y"];/
"Date\Year"\year ["year"];/
"Date\Day"\dayordinal ["dayordinal"];/
"Date\Day"\dayname ["dayname"];/
"Date\Date"\%x ["%x"];/
"Date\Date"\date ["date"];/
"Date\Year"\decade  ["decade"];/
"Date\Day of year"\%j ["%j"];/
"Date\Weekday as decimal /(Sunday=0/)"\%w ["%w"];/
"Date\Week number: Sunday first day of week 1"\%W ["%W"];/
"Date\Week number: Monday first day of week 1"\%U ["%U"];/
"Time\Hour"\h ["h"];/
"Time\Hour"\hh ["hh"];/
"Time\Hour"\%I ["%I"];/
"Time\Hour"\H ["H"];/
"Time\Hour"\HH ["HH"];/
"Time\Hour"\%H ["%H"];/
"Time\Hour"\hour ["hour"];/
"Time\Minute"\m ["m"];/
"Time\Minute"\mm ["mm"];/
"Time\Minute"\%M ["%M"];/
"Time\Minute"\minute ["minute"];/
"Time\Second"\s ["s"];/
"Time\Second"\ss ["ss"];/
"Time\Second"\%S ["%S"];/
"Time\Second"\second ["second"];/
"Time\AM//PM"\t ["t"];/
"Time\AM//PM"\tt ["tt"];/
"Time\AM//PM"\%p ["%p"];/
"Time\Time"\%X ["%X"];/
"Time\Time"\time ["time"];/
"Duration"\elapsed ["elapsed"];/
"Duration"\elapsedago ["elapsedago"];/
"Combined\Date & Time"\%c ["%c"];/
"Combined\Date & Time"\datetime ["datetime"];/
"Combined\Filename friendly"\filename ["filename"];/
"Other"\%% ["%%"];/
)/&datatype=[list]


[vagskal]

For my curiosity, can you elaborate on this?

Sure! (I trust you to put me out of my misery if I have misunderstood something completely.)

I have several fields in MC with ISO dates (stock [Date] for when the recording was released, a field for when the recording was released for the very first time, a field for when the recording was made, a field for when the artist was born and a field for when the artist died) and I contemplate adding others (various chart data). Apart from the stock [Date] field all those fields are string type because I want the ISO date written to the file tags so I can see them in LMS with CustomScan/Browse. The standard ID3v2.3 tag TYER (for original release date) is supported by LMS as standard but impossible to import into MC, hence my repeated requests for such support. Now I use MP3Tag to add the TYER tag and manually copy the information in that tag to a TXXX tag that MC can read.

I want to take the info in my string fields with ISO date data and use it in various ways in custom expression fields as MC internal date values (to easily make fields showing, say, age at recording, age when released, only releases in a persons life time etc. - Geeky I know, but MC is known to cater well for us nerds). I have not found a way to do this. The old workaround with first creating a user data field of the desired type, closing and reopening the options dialog and then change the field to a calculated data field does not work for date type, hence the request for &datatype=[Date].

[MrC]

I'm sure I'll be unable to have any positive affect on misery; so lets see how little damage can be rendered.

The new ConvertDate() accepts an ISO time:

   convertdate(2012-03-24T13:48Z)

   ==> 40992.574999....

It's return can then be used as an internal date (assigned to a type=date field), or you can format the date string and present as you wish:

   formatdate(convertdate(2012-03-24T13:48Z), %Y//%m//%d HH:%M %p)

   ==> 2012/03/24 13:48 PM

Ok, you may assess the damage now.

[vagskal]

It's return can then be used as an internal date (assigned to a type=date field)

That is just it. How can I get the ConvertDate() expression assigned to a type=date field? (Apart from using the static Excel method of filling a user data field.)

[MrC]

I'm probably being daft.

I have a [DATE ISO] field (represents any of your ISO string dates).  It contains the string:

   2012-03-24T13:49Z

See column 1.

I have a [DATE ISO (mc internal)] field, which is of Data Type: Date, Calculated with expression:

  ConvertDate([DATE ISO])

See column 2.

The expression

  FormatDate([DATE ISO (mc internal)], %Y//%m//%d HH:%M %p)

shows the converted date in a pane (you can format your own way of course).  See column 3.

When I change the value in [DATE ISO], the values update correctly.

[vagskal]

OK, I think I begin to understand. The ConvertDate() makes the expression field date type automatically.

But now I cannot get the elapsed part of FormatDate() to work. This expression gives strange results:
FormatDate(Math([Date Died INTERNT]-[Date Born INTERNT]),elapsed)

The Date Born/Died fields contains ISO dates (just the date, no hours) converted to the MC internal format, ConvertDate(1906-12-09) = 2535 and ConvertDate(1983-09-30) = 30589. Taken that example FormatDate(Math(30589-2535),elapsed) reports 35,45 years.

[MrC]

OK, I think I begin to understand. The ConvertDate() makes the expression field date type automatically.

For clarity, a Date in MC is just a floating point number or integer portion, that has special internal rules when a date-type field is displayed and when the MC parser parses dates entering into MC.

But now I cannot get the elapsed part of FormatDate() to work. This expression gives strange results:
FormatDate(Math([Date Died INTERNT]-[Date Born INTERNT]),elapsed)

See my red expression in reply 1 above, and its commentary.  It was a gift-in-advance to you. :-)

[vagskal]

See my red expression in reply 1 above, and its commentary.  It was a gift-in-advance to you. :-)

Aha, It does not work, yet. Thanks for the clarification!

Do you know if IsEqual() works with date type values. I am getting strange results.
If(IsEqual([Date],[Date Died INTERNT],3),Math([Year]-Left([Date Born],4)),Postumt)

Could be a user error. Or does the IsEqual() function not like empty values?

[MrC]

Generally there's a problem with the expression, and I'll be didactic here for possible future reference.

The field token [Date] means the MC formatted version (and is a shortcut for default 1 mode: [Date,1]).  Using [Date,0] means the field's raw data (an integer or floating point number, for this particular field).  The IsEqual() mode you're using is 3, which means numeric compare.  But the first argument to IsEqual(), the interpolated [Date] value will be a string value.

You can see this in play if you test.  Clear a Date field in one file.  Create an expression column with value [Date] and then a second expression column with value:

If(IsEqual([Date], 19, 3),
  EQUAL,
  NOT-EQUAL)

Select the Unassigned value in the first column, and you should see the result of the expression in column 2 is EQUAL.  Clearly not expected.  In fact, you should see that same value is returned for ALL files selected.  Even more unexpected.

Now, replace the [Date] in both expression columns above with [Date,0], and you'll now see the internal representations of the DATE in column 1, and now both EQUAL and NOT-EQUAL values returned in column 2.  Select the Unassigned value in column 1, and EQUAL will be returned; select other values in column 1 and you'll see NOT-EQUAL is returned.  Ok, so different results, but clearly the EQUAL result for the file that has the empty [Date] is meaningless.

Now, remove the [Date,0] value from the second expression column, leaving only the comma argument separator:

If(IsEqual(, 19, 3),
  EQUAL,
  NOT-EQUAL)

All files now return EQUAL, the same result were were getting when [Date] was empty.  Reason: It is simply meaningless to compare a NULL value numerically to an integer/float.  So you need to protect from these types of comparisons.

Fortunately, there's a better, smarter function to use than IsEqual(), and that's Compare().  This will work for your needs:

If(Compare([Date,0], =, <place your other date field here>),
  EQUAL,
  NOT-EQUAL)

[vagskal]

Thanks for the reply!

I will try out the Compare() function instead.

[MrC]

For future reference, I've updated the wiki for ConvertDate(), added the new format specifiers for FormatDate(), and moved these functions plus Now() into a new Date & Time section.  Complaints to me.

[vagskal]

Sorry for bringing this old thread back to life, but I got really puzzled about how escapement works inside arguments in FormatDate(). Matt stated in this thread that quotes should be used for escapement. That seems not to be completely accurate when it comes to the second argument (the actual formatting). It seems to be impossible to escape a comma with quotes, i.e. FormatDate(Now(), yyyy"," HH:mm, No date/time) outputs only the year, while escaping a comma with / seems to work, i.e. FormatDate(Now(), yyyy/, HH:mm, No date or time). This expression FormatDate(Now(), dddden "d"en d MMMM yyyy/, kl. HH:mm) outputs on a Swedish locale "lördagen den 7 juli 2012, kl. 19:48" (dddd=lördag on a Swedish locale), which illustrates the inconsistency.

It also took me a while to find this thread and find out that quotes are used as escapement characters in FormatDate() (but not always, as stated just above). Is it really necessary to use three different escape characters for different functions (FormatDate, RegEx and the other functions)?

On a Swedish locale the day of week (ddd, dd) and month (MMMM, MMM) do not have their initial letter capitalized, but they should have (only) if they are at the beginning of a sentence. Could there be a way to get the initial letter of the day of week and month always capitalized, Dddd for example? I realize that it would be harder to find something suitable for the MMMM and MMM.

Thanks.

[MrC]

Re: escape characters...

Use / to escape characters from the MC expression evaluator.  This form of escapement is required, when using a comma or parenthesis within an MC function.

In some MC functions, the individual function arguments, such the second argument to FormatDate() or Regex(), are passed directly to existing Windows library functions which have their own escapement rules (e.g. " for the date format string, or \ for a regular expression).

This general escapment problem occurs when one system with its own rules relies on other systems that have embedded rules.  It is usually too difficult and error prone for the top-level system to emulate the lower-level parsing grammars.

MC has to parse the expression first, and only then passes what it finds to the underlying code implementing a given function.  So when writing an expression, protect from MC first, then underlying code next.

[vagskal]

Thanks for the explanation. I hope the MC strategy for platform independence will ultimately take care of this. I think Matt confessed they were at the moment using some kind of Microsoft flavour of Regex.

[MrC]

The language of regular expressions and date/time formatting code predates MC, and their language is stable, so this is just something that has to be worked around.

[Matt]

MrC is spot-on here.

Quote escapement in the date formatter is required, because words like day get converted to something special.  So in the event that you want to literally output the word day, there must be some escapement.

I don't really see any way around the escapement-inside-escapement when used with expressions, because you have two different grammars.

[bblue]

Bringing the thread back one more time (apologies), I believe that the simplest form of date conversion, converting the date containing only a year to a YYYY-M format results in (e.g.) 1967-1.  This implies it is the first month, which it may not be.  In my usage, the full date is used (i.e. 5/10/1967) if the exact date is known, but otherwise, just the year is specified.  With the format conversions, it shows the wrong information.  It might be more useful to either return nothing for fields not known, or a zero.

Is there a way to simulate this behavior with the expressions?  I haven't seen it if there is.

--Bill

[MrC]

Test the length of the output of Date - when it is 4, only convert to a Year.  Otherwise, it is safe to use more.

Example:

   http://yabb.jriver.com/interact/index.php?topic=79502.msg540400#msg540400

[bblue]

Test the length of the output of Date - when it is 4, only convert to a Year.  Otherwise, it is safe to use more.

Example:

   http://yabb.jriver.com/interact/index.php?topic=79502.msg540400#msg540400

The examples were set to show each element (day, month, year) in different columns, but as I'm using only one I needed to make some changes.  I ended up with this, which right or wrong, seems to work.

formatdate(yyyy)ifelse(compare(length([date]), >, 4),-formatdate(MM))

Some of the crowding is to keep spaces from printing through.  I wanted it to show 1960  or 1960-8 if more than the year was in the date field.

I know a lot of different languages and script styles but this one is quite foreign.  Have to do some more studying.

Is there a way from MCWS to query for this field?

Thanks for your help!

--Bill

[MrC]

Sorry for the very late reply.

You can save the expression to a new calculated user field.  That field then can be queried using MCWS.

Btw. you can reduce the number of formatdate calls by placing the If() inside:

   formatdate(if(compare(length([date]), >, 4), yyyy-MM, yyyy))