Date Format Specifier Characters in Beast Mode
Intro
In a Beast Mode calculation using a DATE_FORMAT function, you can specify the format to use for a date or time column by specifying the column and the date or time string, as in DATE_FORMAT(`datecolumn`,'format') where datecolumn is the column containing a date value and where format is the string containing specifier characters to use in formatting the date value.
For example, using DATE_FORMAT(`MyDate`,'%Y-%m-%d %h:%i %p'), the date in the MyDate date column uses this format: 2013-04-17 10:10 AM.
The "%" character is required before format specifier characters.
If necessary, you can convert date string values in columns to datetime values using the STR_TO_DATE function.
The format specifiers used in DATE_FORMAT may be used with TIME_FORMAT, but specifiers other than hours, minutes, seconds and microseconds produce a NULL value or 0.
Sample Formats
Format |
Result |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
For more information about adding Beast Mode calculations, see Adding a Beast Mode Calculation to Your Chart.
Date format specifiers
All examples assume a date and time of April 15th, 2013, at 11:44:15 PM.
Specifier |
Description |
Sample |
Result |
---|---|---|---|
|
Abbreviated weekday name (Sun..Sat) |
|
Mon |
|
Abbreviated month name (Jan..Dec) |
|
Apr |
|
Day of month, numeric (00-31) |
|
13 |
|
Microseconds (000000..999999) |
|
300000 |
|
Hour (00-23) |
|
23 |
|
Hour (01-12) |
|
11 |
|
Minutes, numeric (00-59) |
|
44 |
|
Day of year (001-366) |
|
105 |
|
Month name (January..December) |
|
April |
|
Month, numeric (00-12) |
|
04 |
|
AM or PM |
|
PM |
|
Time, 12-hour (hh:mm:ss followed by AM or PM) |
|
11:44:15 PM |
|
Seconds (00-59) |
|
15 |
|
Time, 24-hour (hh:mm:ss) |
|
23:44:15 |
|
ISO week number of the year (the first Thursday of the new year is in week 1) |
|
16 |
|
Weekday name (Sunday..Saturday) |
|
Monday |
|
Day of the week (1-7; Sunday is 1) |
|
2 |
|
ISO year (4 or more digits) |
|
2013 |
|
Year (4 or more digits) |
|
2013 |
|
Last two digits of year |
|
13 |
Unit type values
You can specify unit type values for date or datetime expressions in ADDDATE, DATE_ADD, DATE_SUB, and SUBDATE functions, including those listed in the following table.
Unit Type Value |
Expected Expression Format |
---|---|
MICROSECOND |
MICROSECONDS |
SECOND |
SECONDS |
MINUTE |
MINUTES |
HOUR |
HOURS |
DAY |
DAYS |
WEEK |
WEEKS |
MONTH |
MONTHS |
QUARTER |
QUARTERS |
YEAR |
YEARS |
SECOND_MICROSECOND |
'SECONDS.MICROSECONDS' |
MINUTE_MICROSECOND |
'MINUTES:SECONDS.MICROSECONDS' |
MINUTE_SECOND |
'MINUTES:SECONDS' |
HOUR_MICROSECOND |
'HOURS:MINUTES:SECONDS.MICROSECONDS' |
HOUR_SECOND |
'HOURS:MINUTES:SECONDS' |
HOUR_MINUTE |
'HOURS:MINUTES' |
DAY_MICROSECOND |
'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
DAY_SECOND |
'DAYS HOURS:MINUTES:SECONDS' |
DAY_MINUTE |
'DAYS HOURS:MINUTES' |
DAY_HOUR |
'DAYS HOURS' |
YEAR_MONTH |
'YEARS-MONTHS' |