Skip to content

[FEATURE] Add new data type: PERIOD #862

Open
@Yury-Fridlyand

Description

@Yury-Fridlyand

Is your feature request related to a problem?

MySQL has no such type, at least visible to user, but having a such type we can simplify processing few datetime functions.

... number in YYMMDD, YYMMDDhhmmss, YYYYMMDD, or YYYYMMDDhhmmss format. If the argument includes a time part, it may optionally include a fractional seconds part.

See PERIOD_ADD/PERIOD_DIFF functions in MySQL docs for reference.
The proposed solution would help fixing NOW() + 0 issue, see discussion in #754 and Bit-Quill#92.

What solution would you like?

A new data type PERIOD

  1. Responsible for parsing and formatting
  2. Couldn't be casted to numeric types
  3. Expose a numeric type for user
  4. Has operators + and - overloaded with integers.

This will also simplify UNIX_TIMESTAMP implementation (#835)

try {
var res = LocalDateTime.parse(input, DATE_TIME_FORMATTER_SHORT_YEAR);
return res.toEpochSecond(ZoneOffset.UTC) + fraction;
} catch (DateTimeParseException ignored) {
// nothing to do, try another format
}
try {
var res = LocalDateTime.parse(input, DATE_TIME_FORMATTER_LONG_YEAR);
return res.toEpochSecond(ZoneOffset.UTC) + fraction;
} catch (DateTimeParseException ignored) {
// nothing to do, try another format
}
try {
var res = LocalDate.parse(input, DATE_FORMATTER_SHORT_YEAR);
return res.toEpochSecond(LocalTime.MIN, ZoneOffset.UTC) + 0d;
} catch (DateTimeParseException ignored) {
// nothing to do, try another format
}
try {
var res = LocalDate.parse(input, DATE_FORMATTER_LONG_YEAR);
return res.toEpochSecond(LocalTime.MIN, ZoneOffset.UTC) + 0d;
} catch (DateTimeParseException ignored) {
return null;
}

Other functions affected: all listed in #754, EXTRACT, UTC_DATE, UTC_TIME and UTC_TIMESTAMP (link).

What alternatives have you considered?

Keep using numeric type.

Do you have any additional context?

#855
#722
#835

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions