[Feature Request]: Builtin function FROM_UNIXTIME()

[dengn]

Is there an existing issue for the same feature request?

• [X] I have checked the existing issues.

Is your feature request related to a problem?

Showcase SQL requires the support of this function:

select DATE_FORMAT(FROM_UNIXTIME(tm.create_time), %Y%m%d)

Describe the feature you'd like

Follow MySQL 8.0 syntax.

FROM_UNIXTIME(unix_timestamp[,format])

Returns a representation of unix_timestamp as a datetime or character string value. The value returned is expressed using the session time zone. unix_timestamp is an internal timestamp value representing seconds since '1970-01-01 00:00:00' UTC, such as produced by the UNIX_TIMESTAMP() function.

If format is omitted, this function returns a DATETIME value.

If unix_timestamp is an integer, the fractional seconds precision of the DATETIME is zero. When unix_timestamp is a decimal value, the fractional seconds precision of the DATETIME is the same as the precision of the decimal value, up to a maximum of 6. When unix_timestamp is a floating point number, the fractional seconds precision of the datetime is 6.

On 32-bit platforms, the maximum useful value for unix_timestamp is 2147483647.999999, which returns '2038-01-19 03:14:07.999999' UTC. On 64-bit platforms running MySQL 8.0.28 or later, the effective maximum is 32536771199.999999, which returns '3001-01-18 23:59:59.999999' UTC. Regardless of platform or version, a greater value for unix_timestamp than the effective maximum returns 0.

format is used to format the result in the same way as the format string used for the DATE_FORMAT() function. If format is supplied, the value returned is a VARCHAR.

mysql> SELECT FROM_UNIXTIME(1447430881);
        -> '2015-11-13 10:08:01'
mysql> SELECT FROM_UNIXTIME(1447430881) + 0;
        -> 20151113100801
mysql> SELECT FROM_UNIXTIME(1447430881,
    ->                      '%Y %D %M %h:%i:%s %x');
        -> '2015 13th November 10:08:01 2015'

Describe implementation you've considered

No response

Documentation, Adoption, Use Case, Migration Strategy

No response

Additional information

No response

[dengn]

Prior to implementation of this function, timezone need to be supported as the timestamp issue https://github.com/matrixorigin/matrixone/issues/1871. We will follow the timestamp value range from '1970-01-01 00:00:01.000000' UTC to '2038-01-09 03:14:07.999999' UTC.

[dengn]

Please refer to the corresponding test cases: https://github.com/dengn/mo-tester/blob/main/cases/function/func_datetime_unixtime.test

[JackTan25]

#2985 #2355

[dengn]

Only implemented with one argument, move to 0.6 for a full implementation.

[qingxinhome]

FROM_ The main functions of the UNIXTIME function have been fully implemented, including: FROM_ UNIXTIME(unix_timestamp[,format])

1. If the format format is omitted, this function will return the DATETIME value.
2. If unix_ If the timestamp or format format is NULL, this function returns NULL.
3. If unix_ If timestamp is an integer, the decimal second precision of DATETIME is zero.
4. When unix_ When timestamp is a decimal value, the decimal second precision of DATETIME is the same as that of the decimal value, and the maximum precision is 6. When unix_ When Timestamps is a floating point number, the decimal second precision of the date time is 6.
5. If format parameter is provided, the returned value type is VARCHAR.
6. Format is used to format results, format and DATE_ The format () function uses the same format string.
7. If unix_ The timestamp is a negative number. No matter the single parameter or two parameters, null is returned
8. If unix_ Timestamp>32536771199. No matter single parameter or two parameters, null is returned Hidden danger: Mo will convert the floating point constant to decimal128 and then to double when processing the floating point constant. However, when the significant number of the floating point value exceeds a certain range, the decimal precision will be lost after conversion, Will cause FROM_ UniXTIME calculation results lose precision of subtle decimal places As follows: mysql> select from_ unixtime(32536771196.123456789); +--------------------------------------+ | from_ unixtime(32536771196.123456789) | +--------------------------------------+ | 3001-01-19 07:59:56.123455 | +--------------------------------------+ mysql> explain verbose select from_ unixtime(32536771196.123456789); +--------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------+ | Project (cost=0.00..0.00 card=1.00 ndv=0.00 rowsize=0) | | Output: from_ unixtime(CAST(CAST('32536771196.123456789' AS DECIMAL128) AS DOUBLE)) | | -> Values Scan "VALUES" (cost=0.00..0.00 card=1.00 ndv=0.00 rowsize=0) | | Output: 0 | +--------------------------------------------------------------------------------------+ Expected results: mysql> select from_ unixtime(32536771196.123456789); +--------------------------------------+ | from_ unixtime(32536771196.123456789) | +--------------------------------------+ | 3001-01-19 07:59:56.123457 | +--------------------------------------+ In the later stage, this problem will be avoided by supporting decimal type @dengn @aressu1985 @domingozhang @daviszhen @sukki37

[qingxinhome]

FROM_ UNIXTIME Function has been completed and pr has been submitted. Please know！ @dengn @aressu1985 @daviszhen @sukki37 @domingozhang

[goodMan-code]

This Feature Request has done, can be closed pls, It be tested by manually