v5.0

Date and Time Processing Functions and Operators

Time/Date Functions

Compared with the original MogDB, Dolphin modifies the time/date function as follows:

The dayofmonth, dayofweek, dayofyear, hour, microsecond, minute, quarter, second, weekday, weekofyear, year, and current_date functions are added.
The curdate, current_time, curtime, current_timestamp, localtime, localtimestamp, now, and sysdate functions are added.
The makedate, maketime, period_add, period_diff, sec_to_time, and subdate functions are added.
The subtime, timediff, time, time_format, timestamp, and timestampadd functions are added.
The to_days, to_seconds, unix_timestamp, utc_date, utc_time, and utc_timestamp functions are added.
The date_bool and time_bool functions are added.
The dayname, monthname, time_to_sec, month, day, date, week, yearweek functions are added and the last_day function is modified.
The datediff, from_days, convert_tz, date_add, date_sub, adddate, addtime functions are added and the timestampdiff function is modified.

curdate()

Description: Returns the date when the statement started to be executed.

Return type: date

Example:
```
MogDB=# select curdate();
curdate
------------
2022-07-21
(1 row)
```
current_time

Description: Returns the time when a statement starts to be executed.

Return type: time

Example:
```
MogDB=# select current_time;
current_time
--------------
16:56:02
(1 row)
```

current_time(n)

Description: Returns the time when a statement starts to be executed. n indicates the precision. The maximum value is 6.

Return type: time

Example:

MogDB=# select current_time(3);
 current_time(3)
-----------------
16:57:23.255
(1 row)

MogDB=# select current_time();
 current_time()
----------------
17:05:01
(1 row)

curtime(n)

Description: Returns the time when a statement starts to be executed. n indicates the precision. The maximum value is 6.

Return type: time

Example:
```
MogDB=# select curtime(3);
curtime(3)
--------------
17:45:33.844
(1 row)

MogDB=# select curtime();
curtime()
-----------
17:45:54
(1 row)
```
current_timestamp

Description: Returns the timestamp when a statement starts to be executed.

Return type: datetime

Example:
```
MogDB=# select current_timestamp;
  current_timestamp
---------------------
2022-07-21 16:59:38
(1 row)
```

current_timestamp(n)

Description: Returns the timestamp when the statement starts to be executed. n indicates the precision. The maximum value is 6.

Return type: datetime

Example:

MogDB=# select current_timestamp(3);
  current_timestamp(3)
-------------------------
2022-07-21 17:00:41.251
(1 row)

MogDB=# select current_timestamp();
 current_timestamp()
---------------------
2022-07-21 17:06:06
(1 row)

dayofmonth(timestamp)

Description: Obtains the value of date in the date or time value.

Return type: double precision

Example:
```
MogDB=# SELECT dayofmonth(timestamp '2001-02-16 20:38:40');
 date_part
-----------
        16
(1 row)
```
dayofweek(timestamp)

Description: Obtains the week number in the date/time value. The value 1 indicates Sunday, the value 2 indicates Monday, and the value 7 indicates Saturday.

Return type: double precision

Example:
```
MogDB=# SELECT dayofweek(timestamp '2001-02-16 20:38:40');
 ?column?
----------
        6
(1 row)
```
dayofyear(timestamp)

Description: Obtains the day of a year in a date/time value.

Return type: double precision

Example:
```
MogDB=# SELECT dayofyear(timestamp '2001-02-16 20:38:40');
 date_part
-----------
        47
(1 row)
```
hour(timestamp)

Description: Obtains the value of hour in the date or time value.

Return type: double precision

Example:
```
MogDB=# SELECT hour(timestamp '2001-02-16 20:38:40');
 date_part
-----------
        20
(1 row)
```
localtime

Description: Returns the timestamp when a statement starts to be executed.

Return type: datetime

Example:
```
MogDB=# select localtime;
    localtime
---------------------
2022-07-21 17:02:04
(1 row)
```

localtime(n)

Description: Returns the timestamp when the statement starts to be executed. n indicates the precision. The maximum value is 6.

Return type: datetime

Example:

MogDB=# select localtime(3);
    localtime
---------------------
2022-07-21 17:02:04
(1 row)

MogDB=# select localtime();
    localtime()
---------------------
2022-07-21 17:14:22
(1 row)

localtimestamp

Description: Returns the timestamp when a statement starts to be executed.

Return type: datetime

Example:
```
MogDB=# select localtimestamp;
localtimestamp
---------------------
2022-07-21 17:17:20
(1 row)
```

localtimestamp(n)

Description: Returns the timestamp when the statement starts to be executed. n indicates the precision. The maximum value is 6.

Return type: datetime

Example:

MogDB=# select localtimestamp(3);
    localtimestamp(3)
-------------------------
2022-07-21 17:28:02.013
(1 row)

MogDB=# select localtimestamp();
localtimestamp()
---------------------
2022-07-21 17:28:49
(1 row)

MAKEDATE()

Function prototype:

DATE MAKEDATE(int64 year, int64 dayofyear)

Function description:

Returns the date value of a year when the year and the number of days are given.

Remarks:

If any of them is NULL, the function returns NULL.
The value of dayofyear must be greater than 0. Otherwise, NULL is returned.
0 <= year < 70: year is regarded as 20XX. 70 <= year < 100: year is regarded as 19XX.
The return value ranges from 0 to 9999-12-31. If the return value is out of the range, NULL is returned.

Example:

MogDB=# SELECT MAKEDATE(2022,31), MAKEDATE(2022,32);
   makedate  |  makedate  
------------+------------
 2022-01-31 | 2022-02-01
(1 row)

-- 0<= year < 70 以及 70 <= year < 100
 MogDB=# SELECT MAKEDATE(0,31), MAKEDATE(70,32);
  makedate  |  makedate  
------------+------------
 2000-01-31 | 1970-02-01
(1 row)

-- dayofyear <= 0 以及 超出范围 的情况
 MogDB=# SELECT MAKEDATE(2022,0), MAKEDATE(9999,366);
  makedate | makedate 
----------+----------
          | 
(1 row)

MAKETIME()

Function prototype:
```
TIME MAKETIME(int64 hour, int64 minue, Numeric second)
```
Function description:

Returns a TIME type value when the hour, minute, and second parameters are given.

Remarks:
- The function returns NULL if any of the following conditions is met:
  1. minue < 0 or minue >= 60
  2. second < 0 or second >= 60
  3. Any parameter is NULL.
- The returned value of the TIME type contains six decimal places. If the value of second contains more than six decimal places, the value is rounded off.
- The returned value of the TIME type must be in the range [-838:59:59, 838:59:59]. If the value is out of the range, the specified boundary value is returned based on the positive and negative types of hour.
Example:
```
MogDB=# SELECT MAKETIME(15, 15, 15.5);
  maketime  
------------
 15:15:15.5
(1 row)

-- 四舍五入进位
 MogDB=# SELECT MAKETIME(10, 15, 20.5000005);
    maketime     
-----------------
 10:15:20.500001
(1 row)

-- 四舍五入进位
 MogDB=# SELECT MAKETIME(839,0,0);
 maketime  
-----------
 838:59:59
(1 row)
```
microsecond(timestamp)

Description: Obtains the value of microsecond in the date or time value.

Return type: double precision

Example:
```
MogDB=# SELECT microsecond(timestamp '2001-02-16 20:38:40.123');
 date_part
-----------
    123000
(1 row)
```
minute(timestamp)

Description: Obtains the value of minute in the date or time value.

Return type: double precision

Example:
```
MogDB=# SELECT minute(timestamp '2001-02-16 20:38:40.123');
 date_part
-----------
        38
(1 row)
```

now(n)

Description: Returns the timestamp when the statement starts to be executed. n indicates the precision. The maximum value is 6.

Return type: datetime

Example:

MogDB=# select now(3);
        now(3)
-------------------------
2022-07-21 17:30:18.037
(1 row)

MogDB=# select now();
        now()
---------------------
2022-07-21 17:30:51
(1 row)

PERIOD_ADD()

Function prototype:

int64 PERIOD_ADD(int64 P, int64 N)

Function description:

Return the period P (in YYYYMM or YYMM format) plus N months. The format is YYYYMM.

Remarks:

If any parameter is NULL, the function returns NULL.
If P = 0, 0 is returned.
If the value of P and the year in the returned result is less than 100, 70 is used as the boundary to convert the year to 20XX or 19XX.

Example:

MogDB=# SELECT PERIOD_ADD(202201, 2);
  period_add 
------------
     202203
(1 row)
 
 -- p = 0
 MogDB=# SELECT PERIOD_ADD(0, 2);
 period_add 
------------
          0
(1 row)

 -- 时期的年份处于[0,70) 或 [70, 100)范围内
 MogDB=# SELECT PERIOD_ADD(0101, 2), PERIOD_ADD(7001, 2);
 period_add | period_add 
------------+------------
     200103 |     197003
(1 row)

PERIOD_DIFF()

Function prototype:
```
int64 PERIOD_DIFF(int64 P1, int64 P2)
```
Function description:

Returns the month difference between P1 and P2.

Remarks:
- If any parameter is NULL, the function returns NULL.
- If the year in P1 and P2 is less than 100, 70 is used as the boundary to convert the year to 20XX or 19XX.
Example:
```
MogDB=# SELECT PERIOD_DIFF(202201,202003);
  period_diff 
-------------
          22
(1 row)

 MogDB=# SELECT PERIOD_DIFF(0101,7001);
 period_diff 
-------------
         372
(1 row)
```
quarter(timestamp)

Description: Gets the number of quarters in a date/time value, from 1 to 4.

Return type: double precision

Example:
```
MogDB=# SELECT quarter(timestamp '2001-02-16 20:38:40.123');
 date_part
-----------
        1
(1 row)
```
second(timestamp)

Description: Obtains the value of second in the date or time value.

Return type: double precision

Example:
```
MogDB=# SELECT second(timestamp '2001-02-16 20:38:40.123');
 date_part
-----------
       40
(1 row)
```
SEC_TO_TIME()

Function prototype:
```
TIME SEC_TO_TIME(Numeric second)
```
Function description:

Converts a given number of seconds to hours, minutes, and seconds. This function returns a value of the TIME type.

Remarks:
- If any parameter is NULL, the function returns NULL.
- The returned value of the TIME type contains only six decimal places. The excess part is rounded off.
- The returned value of the TIME type must be in the range [-838:59:59, 838:59:59]. If the value is out of the range, the specified boundary value is returned based on the positive and negative types of second.
Example:
```
MogDB=# SELECT SEC_TO_TIME(4396);
 sec_to_time 
-------------
 01:13:16
(1 row)

-- Round off.
 MogDB=# SELECT SEC_TO_TIME(2378.2222225);
   sec_to_time   
-----------------
 00:39:38.222223
(1 row)

-- The returned result is out of range.
 MogDB=# SELECT SEC_TO_TIME(3888888);
 sec_to_time 
-------------
 838:59:59
(1 row)
```
SUBDATE(expr, interval)

Function prototype:
```
CString SUBDATE(text date, INTERVAL expr unit)
CString SUBDATE(text date, int64 days)
```
Function description:

Performs date calculation. The date parameter specifies the start DATE or DATETIME type value. Specifies the INTERVAL value to be subtracted from the start date. The result date value after subtraction is returned. If the second parameter is an integer, it is considered as a subtracted day value.

Remarks:
- The return format of the function is DATE or DATETIME. Generally, the return type is the same as the type of the first parameter. When the type of the first parameter is DATE and the unit of INTERVAL contains HOUR, MINUTE, and SECOND, the return result is DATETIME.
- The function returns NULL if any of the following conditions is met:
  1. The value of date is out of range [0, 9999-12-31].
  2. Any parameter is NULL.
- The date of the returned result must be within the range [0001-1-1, 9999-12-31]. If the value is out of range, NULL is returned.
Example:
```
MogDB=# SELECT SUBDATE('2022-01-01', INTERVAL 31 DAY), SUBDATE('2022-01-01', 31);
  subdate   |  subdate   
------------+------------
 2021-12-01 | 2021-12-01
(1 row)

-- The first parameter is DATE.
 MogDB=# SELECT SUBDATE('2022-01-01 01:01:01', INTERVAL 1 YEAR);
       subdate       
---------------------
 2021-01-01 01:01:01
(1 row)

-- The first parameter is DATETIME.
 MogDB=# SELECT SUBDATE('2022-01-01 01:01:01', INTERVAL 1 YEAR);
       subdate       
---------------------
 2021-01-01 01:01:01
(1 row)

-- The first parameter is DATE, but the unit of INTERVAL contains TIME.
 MogDB=# SELECT SUBDATE('2022-01-01', INTERVAL 1 SECOND);
       subdate       
---------------------
 2021-12-31 23:59:59
(1 row)
```
SUBDATE(TIME, interval)

Function prototype:
```
TIME SUBDATE(TIME time, INTERVAL expr unit)
TIME SUBDATE(TIME time, int64 days)
```
Function description:

This function is used to be compatible with the scenario where the first parameter type of the subdate function in MySQL can be TIME. In this case, the input of the first parameter must be the original TIME data, not the implicit conversion of the character string. The time parameter specifies the start time of the TIME type. The second parameter specifies the INTERVAL value to be subtracted from the start time. The result date after subtraction is returned. If the second parameter is an integer, it is considered as a subtracted day value.

Remarks:
- The first parameter must be of the original TIME type, not implicitly converted from a string. For example, SUBDATE('1:1:1', 1) does not enter this function. Change it to SUBDATE(time'1:1:1', 1).
- The INTERVAL unit of the second parameter cannot contain the year or month part. Otherwise, NULL is returned.
- The return value must be within [-838:59:59, 838:59:59]. Otherwise, NULL is returned.
Example:
```
 MogDB=# SELECT SUBDATE(time'10:15:20', INTERVAL '1' DAY), SUBDATE(time'10:15:20', 1);
   subdate  |  subdate  
-----------+-----------
 -13:44:40 | -13:44:40
(1 row)

-- The INTERVAL unit of the second parameter cannot contain the year or month part.
 MogDB=# SELECT SUBDATE(time'838:00:00', INTERVAL '1' MONTH);
 subdate 
---------
 
(1 row)

-- The result is out of range.
 MogDB=# SELECT SUBDATE(time'838:59:59', INTERVAL '-1' SECOND);
 subdate 
---------
 
(1 row)
```
SUBTIME()

Function prototype:
```
TEXT SUBTIME(TIME time1, TIME time2)
TEXT SUBTIME(DATETIME time1, TIME time2)
```
Function description:

This function performs date calculation and returns the result of DATETIME or TIME expression time1 minus TIME expression time2. The return parameter type is the same as the input type of time1.

Remarks:
- The value of time1 must be in TIME or DATETIME format. Otherwise, an error is reported.
- The value of time2 must be in the correct and valid TIME format. Otherwise, an error is reported.
- If the return value is greater than [-838:59:59, 838:59:59], the extreme value is returned based on the symbol.
Example:
```
MogDB=# select subtime('11:22:33','10:20:30'); subtime

------

01:02:03 (1 row)

MogDB=# select SUBTIME('2020-03-04 11:22:33', '-10:20:30'); subtime

------

2020-03-04 21:43:03 (1 row) 
```

sysdate(n)

Description: Returns the real-time timestamp of the system. n indicates the precision. The maximum value is 6.

Return type: datetime

Example:

MogDB=# select sysdate(3);
    sysdate(3)
-------------------------
2022-07-21 17:38:23.442
(1 row)

MogDB=# select sysdate();
    sysdate()
---------------------
2022-07-21 17:39:02
(1 row)

time()

Function prototype:
```
Text TIME(TEXT expr)
```
Function description:

The time() function of MySQL is compatible. The parameter specifies a TIME or DATETIME expression from which the time expression is extracted and returned as a string.

Remarks:
- The returned time expression can contain a maximum of six decimal places. The excess part is rounded off.
- For an abnormal date or time format or a date or time with domain overflow (for example, 1:60:60 and 2022-12-32), this function is compatible with the insert statement in MySQL, that is, an error is reported.
- An error is reported for a character string in the date format, and 00:00:00 is returned for a parameter of the date type.
Example:
```
  MogDB=# select time('2022-1-1 1:1:1.1111116'), time('25:25:25');
        time       |   time   
  -----------------+----------
  01:01:01.111112 | 25:25:25
  (1 row)
  
  MogDB=# select time(date'2022-1-1');
    time   
  ----------
  00:00:00
  (1 row)
```

TIMEDIFF()

Function prototype:

TIME TIMEDIFF(TIME time1, TIME time2)
DATETIME TIMEDIFF(DATETIME time1, DATETIME time2)

Function description:

This function performs date calculation and returns the result of subtracting time2 from time1. The type of the returned parameter is the same as the input type.

Remarks:

The types of time1 and time2 must be the same and valid. Otherwise, NULL is returned.
For example, if time1 and time2 are of the TIME type and the return value is beyond [-838:59:59, 838:59:59], the function reports an error.

Example:

  MogDB=# select TIMEDIFF(time'23:59:59',time'01:01:01'), TIMEDIFF(datetime'2008-12-31 23:59:59',datetime'2008-12-30 01:01:01');
   timediff | timediff
  ----------+----------
   22:58:58 | 46:58:58
  (1 row)
  
  -- If the value is out of range, the extreme value is returned.
  MogDB=# SELECT TIMEDIFF(time'-830:00:00', time'10:20:30'), TIMEDIFF(time'830:00:00', time'-10:20:30');
    timediff  | timediff
  ------------+-----------
   -838:59:59 | 838:59:59
  (1 row)

TIMESTAMP()

Function prototype:

DATETIME TIMESTAMP(TEXT expr)
DATETIME TIMESTAMP(TEXT expr, TIME time)

Function description:

If there is only one parameter, the function converts the DATE or DATETIME expression expr to the DATETIME value and returns the value.

If there are two parameters, the function calculates the result of the DATE or DATETIME expression expr plus time of the TIME type and returns the result.

Remarks:

expr is a date or datetime expression that does not exist. For example, '2000-12-32' and '2000-1-1 24:00:00'. The function reports an error.
When the value contains two parameters and the value of the second parameter time is not a character string in TIME format, the function reports an error.

Example:

  MogDB=# select TIMESTAMP('2022-01-01'), TIMESTAMP('20220101');
        timestamp      |      timestamp      
  ---------------------+---------------------
  2022-01-01 00:00:00 | 2022-01-01 00:00:00
  (1 row)
  
  MogDB=# select TIMESTAMP('2022-01-31 12:00:00.123456'), TIMESTAMP('20000229120000.1234567');
          timestamp          |         timestamp          
  ----------------------------+----------------------------
  2022-01-31 12:00:00.123456 | 2000-02-29 12:00:00.123457
  (1 row)
  
  MogDB=# select TIMESTAMP('2022-01-31','12:00:00.123456'), TIMESTAMP('2022-01-31 12:00:00','-32:00:00');
          timestamp          |      timestamp      
  ----------------------------+---------------------
  2022-01-31 12:00:00.123456 | 2022-01-30 04:00:00
  (1 row)
  
  MogDB=# select TIMESTAMP('20000229','100:00:00'), TIMESTAMP('20000229120000.123','100:00:00');
       timestamp      |       timestamp
  ---------------------+-------------------------
   2000-03-04 04:00:00 | 2000-03-04 16:00:00.123
  (1 row)

timestamp_add()

Function prototype:
```
TEXT TIMESTAMP_ADD(text unit, interval span, text datetime)
```
Function description:

Adds a period of time to a known time point. The first parameter unit indicates the time unit, the second parameter span indicates a specific value, and the third parameter datetime indicates a known time point.

Remarks:
- The supported units are as follows:
  
  Unit Input
  
  Year year
  
  Quarter qtr
  
  Month month
  
  Week week
  
  Date day
  
  Hour hour
  
  Minute minute
  
  Second second
  
  Microsecond microsecon
- The span supports decimals. If the unit is second, the span is rounded off to six decimal places based on the seventh decimal place. Otherwise, the span is rounded off to an integer.
- The input type of datetime can be character string, date, datetime, or time.
- The input range of datetime and the calculation result of the function must be within the range [0001-01-01 00:00:00.000000, 9999-12-31 23:59:59.999999]. Otherwise, an error is reported. (This function is compatible with the insert statement in MySQL.)
- For an abnormal date or time format or a date or time with domain overflow (for example, 1:60:60 and 2022-12-32), this function is compatible with the insert statement in MySQL, that is, an error is reported.
Example:
```
  MogDB=# select timestampadd(day, 1, '2022-09-01');
   timestampadd 
  --------------
   2022-09-02
  (1 row)
  
  MogDB=# select timestampadd(hour, 1, '2022-09-01 08:00:00');
      timestampadd     
  ---------------------
   2022-09-01 09:00:00
  (1 row)
```

Unit	Input
Year	year
Quarter	qtr
Month	month
Week	week
Date	day
Hour	hour
Minute	minute
Second	second
Microsecond	microsecon

time_format()

Function prototype:

TEXT TIME_FORMAT(text time, text format)。

Function description:

The first parameter time is a time or datetime expression. The function formats the time value based on the second parameter format and returns the value as a string.

Remarks:

The following formats are supported:

Format	Description
%f	Microsecond (000000 to 999999)
%H	Hour (00 to 23)
%h	Hour (00 to 12)
%I	Hour (00 to 12)
%i	Minute (00 to 59)
%p	AM or PM
%r	The time is in 12-hour AM or PM format (hh:mm:ss AM/PM).
%S	Second (00 to 59)
%s	Second (00 to 59)
%T	Time in 24-hour format (hh:mm:ss)
%k	Hours

For formats that are not related to hour, minute, and second, 0 or NULL is returned, including:

Format Return Result

%a, %b, %D, %j, %M, %U, %u, %V, %v, %W, %w, %X, %x NULL

%c , %e 0

%d, %m, %y 00

%Y 0000

Format	Return Result
%a, %b, %D, %j, %M, %U, %u, %V, %v, %W, %w, %X, %x	NULL
%c , %e	0
%d, %m, %y	00
%Y	0000

The extracted time value can contain a maximum of six decimal places. The excess part is rounded off.

Example:

  MogDB=# select TIME_FORMAT('83:59:59.0000009', '%T|%r|%H|%h|%I|%i|%S|%f|%p|%k');
                    time_format                    
  --------------------------------------------------
  83:59:59|11:59:59 AM|83|11|11|59|59|000001|AM|83
  (1 row)
  
  MogDB=# select TIME_FORMAT('2022-1-1 23:59:59.0000009', '%T|%r|%H|%h|%I|%i|%S|%f|%p|%k');
                    time_format                    
  --------------------------------------------------
  23:59:59|11:59:59 PM|23|11|11|59|59|000001|PM|23

weekday(timestamp)

Description: Obtains the day of a week in the date/time value. The value 0 indicates Monday, the value 1 indicates Tuesday, and the value 6 indicates Sunday.

Return type: double precision

Example:
```
MogDB=# SELECT weekday(timestamp '2001-02-16 20:38:40.123');
 ?column?
----------
        4
(1 row)
```
weekofyear(timestamp)

Description: Obtains the week of a year in a date/time value.

Return type: double precision

Example:
```
MogDB=# SELECT weekofyear(timestamp '2001-02-16 20:38:40.123');
 date_part
-----------
        7
(1 row)
```
year(timestamp)

Description: Obtains the value of year in the date or time value.

Return type: double precision

Example:
```
MogDB=# SELECT year(timestamp '2001-02-16 20:38:40.123');
 year
------
 2001
(1 row)
```

current_date()

Description: Specifies the current date.

Return type: date

Example:

MogDB=# SELECT current_date;
    date    
------------
 2017-09-01
(1 row)

to_days()

Function prototype: int8 TO_DAYS(DATETIME date)

Function description: Receives a date or datetime expression as a parameter and returns the number of days from the date specified by the parameter to the year 0000.

Remarks:
- If the input parameter type is time, the date used for calculation is the current date plus the time specified by time.
- If the entered date is out of the range [0000-01-01, 9999-12-31] or the input parameter is an invalid date or datetime expression, the function reports an error.
Example:
```
  MogDB=# select to_days('0000-01-01');
   to_days 
  ---------
         1
  (1 row)
  
  MogDB=# select to_days('2022-09-05 23:59:59.5');
   to_days 
  ---------
    738768
  (1 row)
  
  -- The current date is 2022-09-05.
  MogDB=# select to_days(time'25:00:00');
   to_days 
  ---------
    738769
  (1 row)
```
to_seconds()

Function prototype: NUMERIC TO_SECONDS(text datetime)

Function description: After you enter a time point datetime, the number of seconds from 0000-01-01 00:00:00 to the time point is returned.

Remarks:
- The datetime parameter supports the following types: character string, number, date, datetime, and time. If the input parameter is of the time type, the date is automatically set to the current date.
- The returned result contains only the integer number of seconds and the decimal part is discarded.
Example:
```
  MogDB=# select to_seconds('2022-09-01');
   to_seconds  
  -------------
   63829209600
  (1 row)
  
  MogDB=# select to_seconds('2022-09-01 12:30:30.888');
   to_seconds  
  -------------
   63829254630
  (1 row)
  
  MogDB=# select to_seconds(20220901123030);
   to_seconds  
  -------------
   63829254630
  (1 row)
```
unix_timestamp()

Function prototype:
```
NUMERIC UNIX_TIMESTAMP()
NUMERIC UNIX_TIMESTAMP(text datetime)
```
Function description:
- If you run the function without entering any parameter, the number of seconds from 1970-01-01 00:00:00 UTC to the current time is returned.
- If you enter a time point datetime, the number of seconds from 1970-01-01 00:00:00 UTC to datetime is returned.
Remarks:
- The datetime parameter supports the following types: character string, number, date, datetime, and time. If the input parameter is of the time type, the date is automatically set to the current date.
- The valid range of the datetime parameter is [1970-01-01 00:00:00.000000 UTC, 2038-01-19 03:14:07.999999 UTC].
- The value range of this parameter is affected by the time zone, but the final calculation result is not affected by the time zone.
- The calculation result can contain a maximum of six decimal places.
Example:
```
  MogDB=# select unix_timestamp('2022-09-01');
   unix_timestamp 
  ----------------
       1661961600
  (1 row)
  
  MogDB=# select unix_timestamp('2022-09-01 12:30:30.888');
   unix_timestamp 
  ----------------
   1662006630.888
  (1 row)
  
  MogDB=# select unix_timestamp(20220901123030.6);
   unix_timestamp 
  ----------------
     1662006630.6
  (1 row)
```
utc_date()

Function prototype: DATE UTC_DATE()

This function is used to return the current UTC date of the DATE type.

Remarks:
- UTC_DATE can be identified as a keyword. In this case, parentheses are not required.
Example:
```
  MogDB=# select UTC_DATE();
    utc_date  
  ------------
   2022-09-06
  (1 row)
  
  MogDB=# select UTC_DATE;
    utc_date  
  ------------
   2022-09-06
  (1 row)
```
utc_time()

Function prototype:

TIME UTC_TIME()

TIME UTC_TIME(int fsp)

Function description: This function is used to return the current UTC time of the TIME type. If an integer parameter is specified as the precision, the number of decimals to be retained in the result can be specified. The supported precision range is [0-6].

Remarks:
- UTC_TIME can be identified by keywords. In this case, parentheses are not required. The effect is the same as that of the UTC_TIME() function without parameters.
Example:
```
  MogDB=# select UTC_TIME();
   utc_time 
  ----------
   15:13:54
  (1 row)
  
  MogDB=# select UTC_TIME(6);
      utc_time    
  ----------------
   15:13:56.59698
  (1 row)
  
  MogDB=# select UTC_TIME;
   utc_time 
  ----------
   15:14:01
  (1 row)
```
utc_timestamp()

Function prototype:
- DATETIME UTC_TIMESTAMP()
- DATETIME UTC_TIMESTAMP(int fsp)
Function description: This function is used to return the current UTC date and time. The type is DATETIME. If an integer parameter is specified as the precision, the number of decimals to be retained in the result can be specified. The supported precision range is [0-6].

Remarks:
- UTC_TIMESTAMP can be identified by keywords. In this case, parentheses are not required. The effect is the same as that of the UTC_TIMESTAMP() function without parameters.
Example:
```
  MogDB=# select UTC_TIMESTAMP();
      utc_timestamp    
  ---------------------
   2022-09-06 15:16:28
  (1 row)
  
  MogDB=# select UTC_TIMESTAMP(6);
         utc_timestamp        
  ----------------------------
   2022-09-06 15:16:34.691118
  (1 row)
  
  MogDB=# select UTC_TIMESTAMP;
      utc_timestamp    
  ---------------------
   2022-09-06 15:16:39
```

date_bool(date)

Description: Returns a Boolean value based on the number of years in a date value. If the value is 0, false is returned. Otherwise, true is returned.

Return type: Boolean

Example:

  MogDB=# select time_bool('18:50:00');
   time_bool 
  -----------
   t
  (1 row)
  MogDB=# select time_bool('00:50:00');
   time_bool 
  -----------
   f
  (1 row)

time_bool(time)

Description: Returns a Boolean value based on the number of years in a date value. If the value is 0, false is returned. Otherwise, true is returned.

Return type: Boolean

Example:

  MogDB=# select date_bool('2022-08-20');
   date_bool 
  -----------
   t
  (1 row)
  MogDB=# select date_bool('0000-08-20');
   date_bool 
  -----------
   f
  (1 row)

dayname(date)

Description: Returns the workday corresponding to the date. The language set of the returned content is controlled by the GUC parameter lc_time_names.

Return type: text

Note: This function is compatible with MySQL table insertion parameters and result constraints.

Example:
```
MogDB=# select dayname('2000-1-1');
dayname
----------
Saturday
(1 row)

MogDB=# alter system set lc_time_names = 'zh_CN';
ALTER SYSTEM SET

MogDB=# select dayname('2000-1-1');
dayname
---------
Saturday
(1 row)
```
monthname(date)

Description: Returns the full name of the month corresponding to the date. The language set of the returned content is controlled by the GUC parameter lc_time_names.

Return type: text

Note: This function is compatible with MySQL table insertion parameters and result constraints.

Example:
```
MogDB=# select monthname('2000-1-1');
monthname
-----------
January
(1 row)

MogDB=# alter system set lc_time_names = 'zh_CN';
ALTER SYSTEM SET

MogDB=# select monthname('2000-1-1');
monthname
-----------
January
(1 row)
```

time_to_sec(time)

Description: Converts time to seconds.

Return type: integer

Note: This function is compatible with MySQL table insertion parameters and result constraints.

Example:

MogDB=# select time_to_sec('838:59:59');
time_to_sec
-------------
    3020399
(1 row)

MogDB=# select time_to_sec('-838:59:59');
time_to_sec
-------------
    -3020399
(1 row)

month(date)

Description: Returns the month of a date.

Return type: integer

Note: This function is compatible with MySQL table insertion parameters and result constraints.

Example:
```
MogDB=# select month('2021-11-12');
month
-------
    11
(1 row)

MogDB=# select month('2021-11-0');
month
-------
    11
(1 row)
```
day(date)

Description: Returns the day of a date.

Return type: integer

Note: This function is compatible with MySQL table insertion parameters and result constraints.

Example:
```
MogDB=# select day('2021-11-12');
day
-----
12
(1 row)

MogDB=# select day('2021-0-0');
day
-----
0
(1 row)
```

date(expr)

Description: Extracts the date part from expr when expr is recognized as a date or datetime expression.

Return type: text

Note: This function is compatible with MySQL table insertion parameters and result constraints.

Example:

MogDB=# select date('2021-11-12');
    date
------------
2021-11-12
(1 row)

MogDB=# select date('2021-11-12 23:59:59.9999999');
    date
------------
2021-11-13
(1 row)

MogDB=# select date('2021-11-0');
    date
------------
2021-11-00
(1 row)

MogDB=# select date('2021-0-3');
    date
------------
2021-00-03
(1 row)

last_day(expr)

Description: Returns the date of the last day of a month when expr is identified as date or datetime.

Return type: date

Note: This function is compatible with MySQL table insertion parameters and result constraints. In B-compatible databases, when the GUC parameter b_compatibility_mode is set to true, this function replaces the original last_day function of MogDB.

Example:
```
MogDB=# set b_compatibility_mode = true;
SET

MogDB=# select last_day('2021-1-30');
last_day
------------
2021-01-31
(1 row)

MogDB=# select last_day('2021-1-0');
last_day
------------
2021-01-31
(1 row)
```

week(date[,mode])

Description: Returns the week of the date represented by the date parameter in a year. The mode parameter is optional. The value range is [0,7]. If no mode parameter is transferred, the GUC parameter default_week_format is used as the default mode parameter.

The following table lists the values and meanings of the mode parameter.

mode	Description
0	Sunday indicates the first day of a week. The value of week ranges from 0 to 53. The first week of a year must contain Sunday.
1	Monday indicates the first day of a week. The value of week ranges from 0 to 53. The first week of a year must contain at least four days in the year.
2	Sunday indicates the first day of a week. The value of week ranges from 1 to 53. The first week of a year must contain Sunday.
3	Monday indicates the first day of a week. The value of week ranges from 1 to 53. The first week of a year must contain at least four days in the year.
4	Sunday indicates the first day of a week. The value of week ranges from 0 to 53. The first week of a year must contain at least four days in the year.
5	Monday indicates the first day of a week. The value of week ranges from 0 to 53. The first week of a year must contain Monday.
6	Sunday indicates the first day of a week. The value of week ranges from 1 to 53. The first week of a year must contain at least four days in the year.
7	Monday indicates the first day of a week. The value of week ranges from 1 to 53. The first week of a year must contain Monday.

For the first day of a week, the value range of week is described as follows:

The first day of a week refers to the first day of a week. Monday or Sunday may be the first day of a week.
The value range of week refers to the value range of the return value of the WEEK function. There are two value ranges: [0-53] and [1-53]. The value 0 in [0-53] indicates that the given date is actually located in the last week of the year before the given date. To associate the returned result with the year of the given date, the given date is considered to be located in the zeroth week of the year (that is, the first week has not started). If you want the week of a given date to be more closely related to its year, use 0, 1, 4, or 5 as the mode value. In this way, when the given date is in the last week of the year, the WEEK function returns 0.
The condition for determining the first week of a year refers to the condition for determining that the given date is in the first week of the current year. Generally, the condition is determined only when the date is at the boundary of the year. There are two determination modes. The mode parameter determines the mode to be used.
- Method 1: If Monday or Sunday is the first day of a week and Monday or Sunday is in the year of the specified date, the week is the first week of the year. The value of mode can be 0, 2, 5, or 7.
- Method 2: If four or more days of the week to which the specified date belongs are in the year to which the specified date belongs, the week is the first week of the year to which the specified date belongs. Otherwise, the week is the last week of the previous year. The value of mode can be 1, 3, 4, or 6.

Return type: integer

Note: This function is compatible with MySQL table insertion parameters and result constraints.

Example:

MogDB=# show default_week_format;
default_week_format
---------------------
0
(1 row)

-- If the specified date is in the last week of the previous year, the value of mode is 0.
MogDB=# select week('2000-1-1');
week
------
    0
(1 row)

MogDB=# alter system set default_week_format = 2;
ALTER SYSTEM SET

-- If the specified date is in the last week of the previous year, the value of mode is 2.
MogDB=# select week('2000-1-1');
week
------
52
(1 row)

MogDB=# select week('2000-1-1', 2);
week
------
52
(1 row)

yearweek(date[,mode])

Description: Returns the year and week of the date represented by the date parameter. mode is an optional integer parameter. The value range is [0,7]. If no mode parameter is specified, 0 is used as the default mode parameter. The GUC parameter default_week_format does not affect the yearweek function. For details about the mode parameter, see the week function.

The yearweek function does not return 0 weeks. That is, the value range of week is always [1-53], which is not affected by the mode parameter.

Return type: bigint

Note: This function is compatible with MySQL table insertion parameters and result constraints.

Example:
```
MogDB=# select week('1987-01-01', 0);
week
------
    0
(1 row)

MogDB=# select yearweek('1987-01-01', 0);
yearweek
----------
198652
(1 row)
```
datediff(expr1,expr2)

Description: expr1 and expr2 can be date or datetime. Calculate the number of days represented by expr1-expr2. Only the date part of expr1 and expr2 is involved in the calculation. If an input parameter is invalid, the function returns NULL.

Return type: integer (indicating the date difference, in days)

Example:
```
  MogDB=# select datediff('2001-01-01','321-02-02');
  datediff 
  ----------
   613576
  (1 row)
```

from_days(N)

Description: Returns the date corresponding to the number of days represented by N.

Return type: date

Example:

  MogDB=# select from_days(365);
  from_days  
  ------------
  0000-00-00
  (1 row)
  
  MogDB=# select from_days(366);
  from_days  
  ------------
  0001-01-01
  (1 row)

timestampdiff(unit,datetime expr1,datetime expr2)

Description: The function returns the values of two date parameters expr2 - expr1. Both parameters may be datetime or date. If the parameter is date, the time part is considered as 0. After the difference is calculated, the calculation result is converted into a specified unit for display. The value of unit can be MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR. If an input parameter is invalid, the function returns NULL.

Return type: bigint (indicating the difference displayed in a specified unit)

Note: In B-compatible databases, this function replaces the original timestampdiff function of MogDB when the GUC parameter b_compatibility_mode is set to true.

Example:
```
  MogDB=# set b_compatibility_mode = true;
  SET
  
  MogDB=# select timestampdiff(SECOND,'2001-01-01 12:12:12','2001-01-01 12:12:11');
  timestampdiff
  ---------------
              -1
  (1 row)
  
  MogDB=# select timestampdiff(MONTH,'2001-01-01 12:12:12','2001-02-01 12:12:12');
  timestampdiff
  ---------------
              1
  (1 row)
```
convert_tz(datetime, from_tz, to_tz)

Description: Converts datetime from the time zone specified by from_tz to the time zone specified by to_tz. If the range of datetime converted from from_tz to the UTC time zone exceeds [1970-01-01 00:00:01.000000, 2038-01-19 03:14:07.999999], the conversion is not performed. If the parameter is invalid, the function returns NULL.

Return value: datetime

Example:
```
  MogDB=# SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
        convert_tz
  ---------------------
    2004-01-01 13:00:00
  (1 row)
```
DATE_ADD(date/datetime/time, interval expr unit)

Function prototype:
```
  text DATE_ADD(text expr1, INTERVAL expr2 unit)
  time DATE_ADD(time expr1, INTERVAL expr2 unit)
```
Description: This function performs the date and time addition operation and returns the result of expr1 plus expr2. expr1 can be data of the date, datetime, or time type, and expr2 indicates the interval value. If expr1 is of the time type, time can be added only when expr1 is of the time type.

Return value type: same as the type of the first parameter.

Remarks:
- Generally, the return type is the same as the type of the first parameter. When the type of the first parameter is DATE and the unit of INTERVAL contains HOUR, MINUTE, and SECOND, the return result is DATETIME.
- Parameter restrictions during MySQL table insertion.
  - If expr1 is in the date or datetime format and the value exceeds [0000-1-1 00:00:00.000000, 9999-12-31 23:59:59.999999], an error is reported.
  - If expr1 is of the time type, time can be added only when expr1 is of the time type. If date_add('1:1:1',interval 1 second) does not enter this function, change it to date_add(time'1:1:1', interval 1 second).
- Result restriction during MySQL table insertion.
  - When expr1 is in the date or datetime format, if the result exceeds [0000-1-1 00:00:00.000000, 9999-12-31 23:59:59.999999], an error is reported. If the result is within this range but less than '0001-1-1 00:00:00.000000', MySQL defines the result as 0000-00-00 or 0000-00-00 xx:xx:xx. The time depends on the calculation result. Because such a result is meaningless, an error is reported in MogDB.
  - For data whose first parameter is of the time type, if the calculation result exceeds the time type range [-838:59:59, 838:59:59], an error is reported.
Example:
```
  MogDB=# SELECT DATE_ADD('2022-01-01', INTERVAL 31 DAY);
    date_add
  ------------
  2022-02-01
  (1 row)
    
  MogDB=# SELECT DATE_ADD('2022-01-01 01:01:01', INTERVAL 1 YEAR);
      date_add       
  ---------------------
  2023-01-01 01:01:01
  (1 row)
    
  MogDB=# SELECT DATE_ADD('2022-01-01', INTERVAL 1 SECOND);
      date_add       
  ---------------------
  2022-01-01 00:00:01
  (1 row)
```
DATE_SUB(date/datetime/time, interval expr unit)

Function prototype:
```
    text DATE_SUB(text expr1, INTERVAL expr2 unit)
    time DATE_SUB(time expr1, INTERVAL expr2 unit)
```
Description: This function performs the date and time subtractive operation and returns the result of expr1 minus expr2. expr1 can be data of the date, datetime, or time type, and expr2 indicates the interval value. If expr1 is of the time type, time can be subtracted only when expr1 is of the time type.

Return value type: same as the type of the first parameter.

Remarks:
- Generally, the return type is the same as the type of the first parameter. When the type of the first parameter is DATE and the unit of INTERVAL contains HOUR, MINUTE, and SECOND, the return result is DATETIME.
- Parameter restrictions during MySQL table insertion.
  - If expr1 is in the date or datetime format and the value exceeds [0000-1-1 00:00:00.000000, 9999-12-31 23:59:59.999999], an error is reported.
  - If expr1 is of the time type, time can be subtracted only when expr1 is of the time type. If date_sub('1:1:1',interval 1 second) does not enter this function, change it to date_sub(time'1:1:1', interval 1 second).
- Result restriction during MySQL table insertion.
  - When expr1 is in the date or datetime format, if the result exceeds [0000-1-1 00:00:00.000000, 9999-12-31 23:59:59.999999], an error is reported. If the result is within this range but less than '0001-1-1 00:00:00.000000', MySQL defines the result as 0000-00-00 or 0000-00-00 xx:xx:xx. The time depends on the calculation result. Because such a result is meaningless, an error is reported in MogDB.
  - For data whose first parameter is of the time type, if the calculation result exceeds the time type range [-838:59:59, 838:59:59], an error is reported.
Example:
```
  MogDB=# SELECT DATE_SUB('2022-01-01', INTERVAL 31 DAY);
    date_sub
  ------------
  2021-12-01
  (1 row)
    
  MogDB=# SELECT DATE_SUB('2022-01-01 01:01:01', INTERVAL 1 YEAR);
      date_sub       
  ---------------------
  2021-01-01 01:01:01
  (1 row)
    
    
  MogDB=# SELECT DATE_SUB('2022-01-01', INTERVAL 1 SECOND);
      date_sub       
  ---------------------
  2021-12-31 23:59:59
  (1 row)
```

ADDDATE(date/datetime/time, interval/days)

Description: Performs a date or time addition operation. When the second parameter is interval, the function is the same as the DATE_ADD function. For details, see DATE_ADD. When the second parameter is an integer, the integer is added to the first parameter as a number of days.

Example:

  MogDB=# SELECT ADDDATE('2021-11-12', INTERVAL 1 SECOND);
        adddate
  ---------------------
  2021-11-12 00:00:01
  (1 row)

  MogDB=# SELECT ADDDATE(time'12:12:12', INTERVAL 1 DAY);
  adddate
  ----------
  36:12:12
  (1 row)

  MogDB=# SELECT ADDDATE('2021-11-12', 1);
    adddate
  ------------
  2021-11-13
  (1 row)

  MogDB=# SELECT ADDDATE(time'12:12:12', 1);
  adddate
  ----------
  36:12:12
  (1 row)

ADDTIME(datetime/time,time)

Function prototype:
```
  time ADDTIME(text expr1, time expr2)
```
Description: This function performs the time addition operation and returns the result of expr1 plus expr2. The expr1 can be in datetime or time format, and expr2 can only be in time format.

Return value type: same as the type of the first parameter.

Remarks:
- Parameter restrictions during MySQL table insertion.
  - If the value of the first parameter is in the datetime format and the value exceeds [0000-1-1 00:00:00.000000, 9999-12-31 23:59:59.999999], an error is reported.
  - If the value of the first parameter is in the time format and exceeds the time range, an error is reported.
  - The value of the second parameter must be in the time format.
- Result restriction during MySQL table insertion.
  - If the result is in datetime format and exceeds [0000-1-1 00:00:00.000000, 9999-12-31 23:59:59.999999], an error is reported. If the result is within this range but is less than '0001-1-1 00:00:00.000000', null is returned.
  - If the result is in the time format and the value exceeds [-838:59:59, 838:59:59], an error is reported.
Example:
```
 MogDB=# SELECT ADDTIME('11:22:33','10:20:30');
  addtime  
  ----------
  21:43:03
  (1 row)
  
  MogDB=# SELECT ADDTIME('2020-03-04 11:22:33', '-10:20:30');
  addtime       
  ---------------------
  2020-03-04 01:02:03
  (1 row)
```

Issue