support time function

[siddontang]

http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

• ADDDATE() Add time values (intervals) to a date value
• ADDTIME() Add time
• CONVERT_TZ() Convert from one timezone to another
• CURDATE() Return the current date
• CURRENT_DATE(), CURRENT_DATE Synonyms for CURDATE()
• CURRENT_TIME(), CURRENT_TIME Synonyms for CURTIME()
• CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP Synonyms for NOW()
• CURTIME() Return the current time
• DATE_ADD() Add time values (intervals) to a date value
• DATE_FORMAT() Format date as specified
• DATE_SUB() Subtract a time value (interval) from a date
• DATE() Extract the date part of a date or datetime expression
• DATEDIFF() Subtract two dates
• DAY() Synonym for DAYOFMONTH()
• DAYNAME() Return the name of the weekday
• DAYOFMONTH() Return the day of the month (0-31)
• DAYOFWEEK() Return the weekday index of the argument
• DAYOFYEAR() Return the day of the year (1-366)
• EXTRACT() Extract part of a date
• FROM_DAYS() Convert a day number to a date
• FROM_UNIXTIME() Format UNIX timestamp as a date
• GET_FORMAT() Return a date format string
• HOUR() Extract the hour
• LAST_DAY Return the last day of the month for the argument
• LOCALTIME(), LOCALTIME Synonym for NOW()
• LOCALTIMESTAMP, LOCALTIMESTAMP() Synonym for NOW()
• MAKEDATE() Create a date from the year and day of year
• MAKETIME() Create time from hour, minute, second
• MICROSECOND() Return the microseconds from argument
• MINUTE() Return the minute from the argument
• MONTH() Return the month from the date passed
• MONTHNAME() Return the name of the month
• NOW() Return the current date and time
• PERIOD_ADD() Add a period to a year-month
• PERIOD_DIFF() Return the number of months between periods
• QUARTER() Return the quarter from a date argument
• SEC_TO_TIME() Converts seconds to 'HH:MM:SS' format
• SECOND() Return the second (0-59)
• STR_TO_DATE() Convert a string to a date
• SUBDATE() Synonym for DATE_SUB() when invoked with three arguments
• SUBTIME() Subtract times
• SYSDATE() Return the time at which the function executes
• TIME_FORMAT() Format as time
• TIME_TO_SEC() Return the argument converted to seconds
• TIME() Extract the time portion of the expression passed
• TIMEDIFF() Subtract time
• TIMESTAMP() With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
• TIMESTAMPADD() Add an interval to a datetime expression
• TIMESTAMPDIFF() Subtract an interval from a datetime expression
• TO_DAYS() Return the date argument converted to days
• TO_SECONDS() Return the date or datetime argument converted to seconds since Year 0
• UNIX_TIMESTAMP() Return a UNIX timestamp
• UTC_DATE() Return the current UTC date
• UTC_TIME() Return the current UTC time
• UTC_TIMESTAMP() Return the current UTC date and time
• WEEK() Return the week number
• WEEKDAY() Return the weekday index
• WEEKOFYEAR() Return the calendar week of the date (1-53)
• YEAR() Return the year
• YEARWEEK() Return the year and week

[hhkbp2]

Is there any test code which could be used to test these time functions? If someone adds support for a time funtion, how to make sure the newly added function is working?

[ngaut]

@hhkbp2 Yes, there are some basic tests:
http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

Some corner tests:
https://github.com/mysql/mysql-server/blob/a2757a60a7527407d08115e44e889a25f22c96c6/mysql-test/t/func_sapdb.test

Results of those above tests:
https://github.com/mysql/mysql-server/blob/a2757a60a7527407d08115e44e889a25f22c96c6/mysql-test/r/func_sapdb.result

[hhkbp2]

@ngaut The tests for mysql would be some good reference. I'm looking for some test codes like unit tests in TiDB to test these time functions, so that newly added time functions could be tested before submitted to the main branch.

[shenli]

@hhkbp2 You can refer to https://github.com/pingcap/tidb/tree/master/expression/builtin/time_test.go
For time builtin functions, the implementations are in time.go and unit tests are in time_test.go.

[shlomi-noach]

May I suggest that unix_timestamp() is a critical function that should be supported, especially in light of from_unixtime() already being implemented.

The current lack of both unix_timestamp() and tiemstampdiff() makes it very difficult to do timestamp comparisons.

[tiancaiamao]

This is a good suggestion and we'll support it soon.
Thanks for feedback. @shlomi-noach

[shenli]

@shlomi-noach unix_timestamp() is already supported. Please try the latest master.

[shlomi-noach]

@shenli thank you -- unix_timestamp() works!

[kaiwu]

omnidb can be handy, but it fails to login tidb because it executes a SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP()) right after authentication and function utc_timestamp() is not supported yet, expect tidb to improve and benefit the eco

[shenli]

@kaiwu Thanks for your feedback!
@zyguan Would you mind add utc_timestamp() for TiDB?

[zyguan]

@shenli Not at all, I will do it later.

[shenli]

@zyguan Great!

[ngaut]

@kaiwu UTC_TIMESTAMP has been supported by the latest master, please try it. Thanks.

[kaiwu]

@ngaut yes, i confirm the fix 395a9c6 solves the issue raised earlier, omnidb client can login to Tidb and function UTC_TIMESTAMP works just fine, THANKS guys !

[zz-jason]

already supported