Date and Time Functions

See also: Expression Language and Function Index

Media Center provides several functions for the conversion, formatting and generation of dates and times. Date and time for a Date-type field is stored internally as a single floating-point number, where the integer portion represents the number of days since the Epoch, and the decimal portion represents the fraction of a day in seconds. The Epoch is defined as December 30th, 1899 at 00:00:01. Certain fractional values and whole numbers are used to encode Time-only and Year-only values. For example, the internal Date value of "2" is considered as 1900, without a time when converted using the DateTime conversion format of FormatDate(), whereas adding a small fraction and using "2.001" instead produces a value of "1/1/1900 12:01 am". These details are only relevant if you are doing conversions.

The Windows locale setting will affect the interpretation and formatting of date and time information.

CompareDates(…)

Compares two dates, returning a formatted elapsed period between them.

Description

CompareDates(Date 1, Date 2, Mode) Argument Mode is optional (defaults to a). The CompareDates() function compares Date 1 with Date 2 returning the elapsed period between the two. The Modes below can be used to format the value returned by the function. Available mode values: a Automatic c Calendar Years y Decimal Years d Decimal Days yd Years and Days Notes: Modes a and c return rounded results, so, for example, 37.95 years is returned as 38 years, not 37 years. Leap years are factored in automatically, though there are currently (Media Center 30.0.61) still some rounding errors that may affect your expected result. See this Interact Forum post for full details.

Examples

* All examples shown below use the UK date system of dd/mm/yyyy hh:mm CompareDates(ConvertDate(10//03//2018 15:25),ConvertDate(11//02//2023)) No mode is specified, so defaults to a (Automatic), returning decimal years to two decimal places, 4.93 years. Note that the forward slash in the date values needs to be escaped, hence the 'double forward slash' shown. CompareDates([date imported,0],[last played,0]) Any 'Date' field can be used for comparison, but the raw field data must be specified by including the ,0 instruction. Maths and dates can be troublesome See this Interact forum thread for more information.

(Back to top)   (Function index)

ConvertDate(…)

Converts a human-readable date to the internal format required for use in date fields.

Description

convertdate(date_time string) Converts a human-readable date_time string into the internal floating-point representation used by Media Center to store a date and time. It is also possible to break the 'human-readable' date_time string into components and pass these to the function, like so: convertdate(year,month,day,hour,minute,second)

Examples

convertdate(3//6//2012) Returns the value 40974, which is the internal floating-point representation of the date string 3/6/2012. This value can used by any field of type Date, or in any function that requires as input a Date type value. formatdate(convertdate(May 17 1970), dddd dd MMM yyyy) Returns the value Sunday 17 May 1970, as the input does not need to be numeric, but can be in common human readable format, including "17 May 17", or "17-5-17", or "17 5 17", which all mean Wednesday 17 May 2017. Or "17 5", where the year is left out and defaults to the current year. Or "17 17", where the month is left out and default to the current month. formatdate(convertdate(12//2//1985), decade)) Converts the date string 12/2/1985 (note: December 2nd, not February 12th, though the function appears to follow system date formatting) into a Date type value, and then formats the result as a decades grouping, returning 1980's. This might be used for creating decade groupings.

(Back to top)

DateInRange(…)

Functions as a switch or select case statement.

Description

DateInRange(Date, Range Start, Range End) The DateInRange() function compares a date against a range of dates, returning "1" if the date is within the given range, and "0" if it is not. Notes: The range is not inclusive. A search using a range of 2010 to 2019 will return matches from 2010 to 2018. The start and end dates can be formatted in more than one fashion, though must follow the dd-mm-yyyy hh:mm template used on the system the expression is to be run on. Time can also be included in the range. Related: See also IsRange(…)

Examples

* All examples shown below use the UK date system of dd/mm/yyyy hh:mm DateInRange([Date],1980,1990) Returns "1" for any date within the years from 1980 to 1989, and "0" for all other dates. DateInRange([Date],01//03//2012,31//03//2012) Returns "1" for any date during March 2012, and "0" for all other dates. Note that here, the forward slashes need to be escaped DateInRange([Date],01-03-2012,31-03-2012) Returns "1" for any date during March 2012, and "0" for all other dates. Exactly as the example above, showing different range formatting allowed. if(DateInRange([Date],17-03-2012 00:00,17-03-2012 12:00),The morning of March 17th 2012,Some other time) Returns "The morning of March 17th 2012" for any date between 00:00 and 11:59 on March 17th 2012, and "Some other time" for all other dates.

(Back to top)   (Function index)

FormatDate(…)

Formats a date value in a specified manner.

Description

formatdate(date value, format specifier, empty label) The FormatDate() function provides custom formatting of date and time values through the use of a format specifier. Output will be formatted according to format specifier. The date value is a Media Center internal floating-point date/time representation, stored in Date fields, and output from various functions such as Now() and ConvertDate(). To pass a field of type Date to FormatDate(), use the raw (unformatted) field specification, such as "[Date Imported,0]". The format specifier provides a recipe for converting the internal value into a human-readable string. Supported are a variety of Windows style, strftime() style, and Media Center-specific formats specifiers. Construct the format specifier from any number or combination of those defined in the following table. Additionally, any non-format characters will be output without interpretation. This allows creating rich date/time output strings. To output a word that is a reserved format specifier, surround the word with double-quotes. The empty label argument will be output if the date value is empty. Specifier Description Day d Day Day of the month as digits without leading zeros for single-digit days. dd %d Day of the month as digits with leading zeros for single-digit days. ddd %a Day of the week, abbreviated to three letters. dddd %A Dayname Day of the week. %w Day of the week as a decimal number, Sunday as 0 (0-6). %j Day of the year (001-366) %j Dayordinal Ordinal day of the month (e.g. 1st, 2nd, etc.). Month M Month as digits without leading zeros for single-digit months. MM %m Month as digits with leading zeros for single-digit months. MMM %b Abbreviated month name, three letters (e.g. Apr, Nov). MMMM %B Month Full Month name (e.g. April, November). Year y Year represented only by the last digit. yy %y Year represented only by the last two digits. A leading zero is added for single-digit years. yyyy %Y Year Year represented by a full four or five digits. Decade Year expressed as a decade (e.g. 1970's, 2010's) Hour h Hour Hours with no leading zero for single-digit hours; 12-hour clock. hh %I Hours with leading zero for single-digit hours; 12-hour clock. H Hours with no leading zero for single-digit hours; 24-hour clock. HH %H Hours with leading zero for single-digit hours; 24-hour clock. Minute m Minute Minutes with no leading zero for single-digit minutes. mm %M Minutes with leading zero for single-digit minutes. Second s Second Seconds with no leading zero for single-digit seconds. ss %S Seconds with leading zero for single-digit seconds. AM/PM t One character time marker string, such as A or P. tt %p Multi-character time marker string, such as AM or PM. Combined %x Date Date expressed in the system's format. %X Time Time expressed in the system's format. %c DateTime Date and time expressed in the system's format. ShortDate Age-conditional date formatted as one of "Year", "MMM d", or "MMM d Year". ShortDateTime Date in ShortDate format plus Time. Week Number %U Week number with the first Sunday as the first day of week one (00-53). %W Week number with the first Monday as the first day of week one (00-53). Miscellaneous Elapsed Time expressed as elapsed time (e.g. 2.5 hours). ElapsedAgo Time expressed as elapsed time ago (e.g. 2.5 hours ago). Filename Date and time expressed in filename-friendly format, includes seconds to avoid filename collisions (e.g. 20040521-032221). %% A percent (%) character. Argument date value is optional (defaults to [date,0]). Argument empty label is optional (defaults to EMPTY).

Examples

formatdate(year-month) Outputs the file's date formatted as Year-Month, such as 2012-April. The default date value of [Date,0] is used. formatdate([last played,0], yyyy//MM//dd, Not Yet) Returns the file's last played date as year/month/day without the time, ignoring the system locale setting. If a file has no last played value, the expression will output Not Yet instead. formatdate([date modified,0], month %Y) Returns the file's modification date/time in the form of a long month name and a four-digit year, such as December 2010. formatdate([date imported,0], The "year" is year) Outputs the The year is ####, where #### is the year the file was imported into the Library. Note that the word year must be surrounded in double-quotes to have it considered as literal text, and not the Year format specifier. formatdate([date imported,0], month)&datatype=[month] This examples is the same as the previous example, but includes a cast to the Month type &datatype=[month]. This cast can be used to cause chronological month-sorting, rather than month name alphabetic-sorting, in a panes or category view. Data-type coercion is discussed above. Additional Examples Date formatting development discussion, usage tips and examples.

(Back to top)

Now(…)

Retrieve and display the system date.

Description

now() The Now() function returns a floating-point value representing the current system date and time. It is generally useful for performing date arithmatic in expressions that desire to figure out elapsed time. Any raw date field or value representing a date can be subtracted from Now() to realize an elapsed time delta.

Examples

now() When run on Aug 17, 2013 at 19:28:00, returns approximately 41503.811115995. formatdate(now(), date) Returns the current date, without a time component, formatted according to the system's locale settings. formatdate(math(now() - 3, dddd dd MMMM yyyy H:mm) The date from three days ago is formatted as something like Wednesday 14 August 2013 19:35. This is accomplished by subtracting the value 3, which would be days, from Now(), and its output formatted by FormatDate().

PlaylistTime(…)

Returns the time of a track in the current playlist (a sum of all previous durations)

Description

PlaylistTime(Mode, Format) The PlaylistTime() function provides running totals of time in the current playlist. Totals returned are determined by the specified Mode value. Argument Mode is optional (defaults to 0). Available Mode values: 0 Returns a sum of all previous durations 1 Returns a sum of all previous durations, including the current item 2 Returns the remaing time of the list 3 Returns the remaing time of the list, including the current item 4 Returns the total playlist duration Argument Format is optional (defaults to 1). Available Format values: 0 Returns unformatted, raw data (milliseconds) 1 Returns formatted, human readable data (time) NOTE: This function's primary use will be as an expression column in a file list. Be aware that if used in a list of tens of thousands of files, the view will struggle with the calculations and become sporadically unresponsive.

Examples

PlaylistTime() The table below shows list expression columns using modes 0 - 4: Name Duration Mode 0 Mode 1 Mode 2 Mode 3 Mode 4 Tangled Up In Blue 6:54 0:00 6:55 17:41 24:36 24:36 Simple Twist Of Fate 4:23 6:54 11:18 13:18 17:41 24:36 You're A Big Girl Now 4:24 11:18 15:43 8:53 13:18 24:36 Idiot Wind 8:53 15:43 24:36 0:00 8:53 24:36 Values in the table above have been lifted directly from a Media Center list, indicating that the function is performing some rounding up in certain stuations.

(Back to top)