Skip to content

Expand date formatters and add DATE_FORMAT function for better MySQL compatibility #46302

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
rschu1ze merged 1 commit into from
Feb 16, 2023
Merged

Expand date formatters and add DATE_FORMAT function for better MySQL compatibility #46302

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
14 changes: 13 additions & 1 deletion docs/en/sql-reference/functions/date-time-functions.md
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1205,6 +1205,8 @@ Formats a Time according to the given Format string. Format is a constant expres

formatDateTime uses MySQL datetime format style, refer to https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format.

Alias: `DATE_FORMAT`.

**Syntax**

``` sql
Expand All @@ -1220,29 +1222,39 @@ Using replacement fields, you can define a pattern for the resulting string. “

| Placeholder | Description | Example |
|----------|---------------------------------------------------------|------------|
| %a | abbreviated weekday name (Mon-Sun) | Mon |
| %b | abbreviated month name (Jan-Dec) | Jan |
| %c | month as a decimal number (01-12) | 01 |
| %C | year divided by 100 and truncated to integer (00-99) | 20 |
| %d | day of the month, zero-padded (01-31) | 02 |
| %D | Short MM/DD/YY date, equivalent to %m/%d/%y | 01/02/18 |
| %e | day of the month, space-padded ( 1-31) |   2 |
| %e | day of the month, space-padded (1-31) |   2 |
| %f | fractional second from the fractional part of DateTime64 | 1234560 |
| %F | short YYYY-MM-DD date, equivalent to %Y-%m-%d | 2018-01-02 |
| %G | four-digit year format for ISO week number, calculated from the week-based year [defined by the ISO 8601](https://en.wikipedia.org/wiki/ISO_8601#Week_dates) standard, normally useful only with %V | 2018 |
| %g | two-digit year format, aligned to ISO 8601, abbreviated from four-digit notation | 18 |
| %h | hour in 12h format (01-12) | 09 |
| %H | hour in 24h format (00-23) | 22 |
| %i | minute (00-59) | 33 |
| %I | hour in 12h format (01-12) | 10 |
| %j | day of the year (001-366) | 002 |
| %k | hour in 24h format (00-23) | 22 |
| %l | hour in 12h format (01-12) | 09 |
| %m | month as a decimal number (01-12) | 01 |
| %M | minute (00-59) | 33 |
| %n | new-line character (‘’) | |
| %p | AM or PM designation | PM |
| %Q | Quarter (1-4) | 1 |
| %r | 12-hour HH:MM AM/PM time, equivalent to %H:%M %p | 10:30 PM |
| %R | 24-hour HH:MM time, equivalent to %H:%M | 22:33 |
| %s | second (00-59) | 44 |
| %S | second (00-59) | 44 |
| %t | horizontal-tab character (’) | |
| %T | ISO 8601 time format (HH:MM:SS), equivalent to %H:%M:%S | 22:33:44 |
| %u | ISO 8601 weekday as number with Monday as 1 (1-7) | 2 |
| %V | ISO 8601 week number (01-53) | 01 |
| %w | weekday as a decimal number with Sunday as 0 (0-6) | 2 |
| %W | full weekday name (Monday-Sunday) | Monday |
| %y | Year, last two digits (00-99) | 18 |
| %Y | Year | 2018 |
| %z | Time offset from UTC as +HHMM or -HHMM | -0500 |
Expand Down
163 changes: 134 additions & 29 deletions src/Functions/formatDateTime.cpp
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -342,12 +342,51 @@ class FunctionFormatDateTimeImpl : public IFunction
return writeNumber2(dest, ToMonthImpl::execute(source, timezone));
}

static size_t monthOfYearText(char * dest, Time source, bool abbreviate, UInt64, UInt32, const DateLUTImpl & timezone)
{
auto month = ToMonthImpl::execute(source, timezone);
std::string_view str_view = abbreviate ? monthsShort[month - 1] : monthsFull[month - 1];
memcpy(dest, str_view.data(), str_view.size());
return str_view.size();
}

static size_t mysqlMonthOfYearTextShort(char * dest, Time source, UInt64 fractional_second, UInt32 scale, const DateLUTImpl & timezone)
{
return monthOfYearText(dest, source, true, fractional_second, scale, timezone);
}

static size_t mysqlMonthOfYearTextLong(char * dest, Time source, UInt64 fractional_second, UInt32 scale, const DateLUTImpl & timezone)
{
return monthOfYearText(dest, source, false, fractional_second, scale, timezone);
}

static size_t mysqlDayOfWeek(char * dest, Time source, UInt64, UInt32, const DateLUTImpl & timezone)
{
*dest = '0' + ToDayOfWeekImpl::execute(source, 0, timezone);
return 1;
}

static size_t dayOfWeekText(char * dest, Time source, bool abbreviate, UInt64, UInt32, const DateLUTImpl & timezone)
{
auto week_day = ToDayOfWeekImpl::execute(source, 0, timezone);
if (week_day == 7)
week_day = 0;

std::string_view str_view = abbreviate ? weekdaysShort[week_day] : weekdaysFull[week_day];
memcpy(dest, str_view.data(), str_view.size());
return str_view.size();
}

static size_t mysqlDayOfWeekTextShort(char * dest, Time source, UInt64 fractional_second, UInt32 scale, const DateLUTImpl & timezone)
{
return dayOfWeekText(dest, source, true, fractional_second, scale, timezone);
}

static size_t mysqlDayOfWeekTextLong(char * dest, Time source, UInt64 fractional_second, UInt32 scale, const DateLUTImpl & timezone)
{
return dayOfWeekText(dest, source, false, fractional_second, scale, timezone);
}

static size_t mysqlDayOfWeek0To6(char * dest, Time source, UInt64, UInt32, const DateLUTImpl & timezone)
{
auto day = ToDayOfWeekImpl::execute(source, 0, timezone);
Expand Down Expand Up @@ -411,6 +450,16 @@ class FunctionFormatDateTimeImpl : public IFunction
return 5;
}

static size_t mysqlHHMM12(char * dest, Time source, UInt64, UInt32, const DateLUTImpl & timezone)
{
auto hour = ToHourImpl::execute(source, timezone);
writeNumber2(dest, hour == 0 ? 12 : (hour > 12 ? hour - 12 : hour));
writeNumber2(dest + 3, ToMinuteImpl::execute(source, timezone));

dest[6] = hour >= 12 ? 'P' : 'A';
return 8;
}

static size_t mysqlSecond(char * dest, Time source, UInt64, UInt32, const DateLUTImpl & timezone)
{
return writeNumber2(dest, ToSecondImpl::execute(source, timezone));
Expand Down Expand Up @@ -503,15 +552,10 @@ class FunctionFormatDateTimeImpl : public IFunction
return writeNumberWithPadding(dest, week_day, min_represent_digits);
}

static size_t jodaDayOfWeekText(size_t min_represent_digits, char * dest, Time source, UInt64, UInt32, const DateLUTImpl & timezone)
static size_t jodaDayOfWeekText(size_t min_represent_digits, char * dest, Time source, UInt64 fractional_second, UInt32 scale, const DateLUTImpl & timezone)
{
auto week_day = ToDayOfWeekImpl::execute(source, 0, timezone);
if (week_day == 7)
week_day = 0;

std::string_view str_view = min_represent_digits <= 3 ? weekdaysShort[week_day] : weekdaysFull[week_day];
memcpy(dest, str_view.data(), str_view.size());
return str_view.size();
bool abbreviate = min_represent_digits <= 3;
return dayOfWeekText(dest, source, abbreviate, fractional_second, scale, timezone);
}

static size_t jodaYear(size_t min_represent_digits, char * dest, Time source, UInt64, UInt32, const DateLUTImpl & timezone)
Expand Down Expand Up @@ -551,12 +595,10 @@ class FunctionFormatDateTimeImpl : public IFunction
return writeNumberWithPadding(dest, month_of_year, min_represent_digits);
}

static size_t jodaMonthOfYearText(size_t min_represent_digits, char * dest, Time source, UInt64, UInt32, const DateLUTImpl & timezone)
static size_t jodaMonthOfYearText(size_t min_represent_digits, char * dest, Time source, UInt64 fractional_second, UInt32 scale, const DateLUTImpl & timezone)
{
auto month = ToMonthImpl::execute(source, timezone);
std::string_view str_view = min_represent_digits <= 3 ? monthsShort[month - 1] : monthsFull[month - 1];
memcpy(dest, str_view.data(), str_view.size());
return str_view.size();
bool abbreviate = min_represent_digits <= 3;
return monthOfYearText(dest, source, abbreviate, fractional_second, scale, timezone);
}

static size_t jodaDayOfMonth(size_t min_represent_digits, char * dest, Time source, UInt64, UInt32, const DateLUTImpl & timezone)
Expand Down Expand Up @@ -909,6 +951,24 @@ class FunctionFormatDateTimeImpl : public IFunction

switch (*pos)
{
// Abbreviated weekday [Mon...Sun]
case 'a':
instructions.emplace_back(&Action<T>::mysqlDayOfWeekTextShort);
out_template += "Mon";
break;

// Abbreviated month [Jan...Dec]
case 'b':
instructions.emplace_back(&Action<T>::mysqlMonthOfYearTextShort);
out_template += "Jan";
break;

// Month as a decimal number (01-12)
case 'c':
instructions.emplace_back(&Action<T>::mysqlMonth);
out_template += "00";
break;

// Year, divided by 100, zero-padded
case 'C':
instructions.emplace_back(&Action<T>::mysqlCentury);
Expand Down Expand Up @@ -990,6 +1050,12 @@ class FunctionFormatDateTimeImpl : public IFunction
out_template += "0";
break;

// Full weekday [Monday...Sunday]
case 'W':
instructions.emplace_back(&Action<T>::mysqlDayOfWeekTextLong);
out_template += "Monday";
break;

// Two digits year
case 'y':
instructions.emplace_back(&Action<T>::mysqlYear2);
Expand Down Expand Up @@ -1028,65 +1094,102 @@ class FunctionFormatDateTimeImpl : public IFunction
out_template += "AM";
break;

// 24-hour HH:MM time, equivalent to %H:%M 14:55
// 12-hour HH:MM time, equivalent to %h:%i %p 2:55 PM
case 'r':
add_instruction_or_extra_shift(&Action<T>::mysqlHHMM12, 8);
out_template += "12:00 AM";
break;

// 24-hour HH:MM time, equivalent to %H:%i 14:55
case 'R':
add_instruction_or_extra_shift(&Action<T>::mysqlHHMM24, 5);
out_template += "00:00";
break;

// Seconds
case 's':
add_instruction_or_extra_shift(&Action<T>::mysqlSecond, 2);
out_template += "00";
break;

// Seconds
case 'S':
add_instruction_or_extra_shift(&Action<T>::mysqlSecond, 2);
out_template += "00";
break;

// ISO 8601 time format (HH:MM:SS), equivalent to %H:%M:%S 14:55:02
// ISO 8601 time format (HH:MM:SS), equivalent to %H:%i:%S 14:55:02
case 'T':
add_instruction_or_extra_shift(&Action<T>::mysqlISO8601Time, 8);
out_template += "00:00:00";
break;

// Hour in 12h format (01-12)
case 'h':
add_instruction_or_extra_shift(&Action<T>::mysqlHour12, 2);
out_template += "12";
break;

// Hour in 24h format (00-23)
case 'H':
add_instruction_or_extra_shift(&Action<T>::mysqlHour24, 2);
out_template += "00";
break;

// Minute of hour range [0, 59]
case 'i':
add_instruction_or_extra_shift(&Action<T>::mysqlMinute, 2);
out_template += "00";
break;

// Hour in 12h format (01-12)
case 'I':
add_instruction_or_extra_shift(&Action<T>::mysqlHour12, 2);
out_template += "12";
break;

/// Escaped literal characters.
case '%':
add_extra_shift(1);
out_template += "%";
// Hour in 24h format (00-23)
case 'k':
add_instruction_or_extra_shift(&Action<T>::mysqlHour24, 2);
out_template += "00";
break;

// Hour in 12h format (01-12)
case 'l':
add_instruction_or_extra_shift(&Action<T>::mysqlHour12, 2);
out_template += "12";
break;

case 't':
add_extra_shift(1);
out_template += "\t";
break;

case 'n':
add_extra_shift(1);
out_template += "\n";
break;

// Escaped literal characters.
case '%':
add_extra_shift(1);
out_template += "%";
break;

// Unimplemented
case 'U':
[[fallthrough]];
case 'W':
throw Exception(
ErrorCodes::NOT_IMPLEMENTED,
"Wrong syntax '{}', symbol '{}' is not implemented for function {}",
format,
*pos,
getName());
throw Exception(ErrorCodes::NOT_IMPLEMENTED, "format is not supported for WEEK (Sun-Sat)");
case 'v':
throw Exception(ErrorCodes::NOT_IMPLEMENTED, "format is not supported for WEEK (Mon-Sun)");
case 'x':
throw Exception(ErrorCodes::NOT_IMPLEMENTED, "format is not supported for YEAR for week (Mon-Sun)");
case 'X':
throw Exception(ErrorCodes::NOT_IMPLEMENTED, "format is not supported for YEAR for week (Sun-Sat)");

default:
throw Exception(
ErrorCodes::ILLEGAL_COLUMN,
"Wrong syntax '{}', unexpected symbol '{}' for function {}",
ErrorCodes::BAD_ARGUMENTS,
"Incorrect syntax '{}', symbol is not supported '{}' for function {}",
format,
*pos,
getName());
Expand Down Expand Up @@ -1337,6 +1440,8 @@ using FunctionFromUnixTimestampInJodaSyntax = FunctionFormatDateTimeImpl<NameFro
REGISTER_FUNCTION(FormatDateTime)
{
factory.registerFunction<FunctionFormatDateTime>();
factory.registerAlias("DATE_FORMAT", FunctionFormatDateTime::name);

factory.registerFunction<FunctionFromUnixTimestamp>();
factory.registerAlias("FROM_UNIXTIME", "fromUnixTimestamp");

Expand Down
8 changes: 8 additions & 0 deletions tests/queries/0_stateless/00718_format_datetime.reference
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,25 +1,33 @@
Tue Tue
Jan Jan
01 01
20 20
02 02
01/02/18 01/02/18
2 2
2018-01-02 2018-01-02
10 12
22 00
02
33 00
10 12
11
12
001 001
366 366
00 00
01 01
33 00
\n \n
AM AM
AM
PM
10:33 PM 12:00 AM
22:33 00:00
44 00
\t \t
22:33:44 00:00:00
Tuesday Tuesday
1 7 1 7
01 01 53 52 01 01 53 52
1 0 1 0
Expand Down
Loading