F.52. myfce
Prev UpAppendix F. Additional Supplied ModulesHome Next

F.52. myfce

F.52.1. Features Compatible with Mysql Databases
F.52.2. SQL Functions Reference

myfce - Mysql's compatibility functions.

F.52.1. Features Compatible with Mysql Databases

Features compatible with Mysql databases are provided. These features enable you to easily migrate to LightDB and reduce the costs of reconfiguring applications. The table below lists features compatible with Mysql databases.

Table F.30. Data type

Item

Overview

LONGTEXT

LONGTEXT is an alias for TEXT.

SIGNED

SIGNED is a signed integer, equivalent to bigint.


Table F.31. Mathematical Functions

Item

Overview

CONV

Convert numbers between different number bases

LOG10

Return the base-10 logarithm of the argument

LOG2

Return the base-2 logarithm of the argument

RAND

Returns a random floating-point value v in the range 0 and 1.0

TRUNCATE

Truncate to specified number of decimal places


Table F.32. String Functions

Item

Overview

ELT

Return string at index number

FIELD

Index (position) of first argument in subsequent arguments

FIND_IN_SET

Index (position) of first argument within second argument

FROM_BASE64

Decode base64 encoded string and return result

INSERT

Insert substring at specified position up to specified number of characters

INSTR

Get the first occurrence position of the substring

LCASE

Synonym for LOWER()

LOCATE

Return the position of the first occurrence of substring

MID

Return a substring starting from the specified position

SPACE

Return a string of the specified number of spaces

STRCMP

Compare two strings

TO_BASE64

Return the argument converted to a base-64 string

UCASE

Synonym for UPPER()


Table F.33. Date/Time Functions

Item

Overview

DATEDIFF

Subtract two dates

DATE_ADD

Add the specified time interval to the date

DATE_FORMAT

Return date / time data in different formats

DATE_SUB

Subtracts the specified time interval from the date

DAY

Returns the date part of the date month

DAYOFWEEK

Returns the day of the week index of the current week, starting at 1

FROM_UNIXTIME

Returns the date / datetime representation of the UNIX timestamp

SEC_TO_TIME

Converts seconds to 'hh:mm:ss' format

STR_TO_DATE

Convert string to date / time value

TIME_TO_SEC

Return the argument converted to seconds

TIMEDIFF

Subtract time

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

Returns the UNIX timestamp of an exact point in time

WEEKOFYEAR

Returns the number of weeks of the date

YEAR

Returns the year of the date


Table F.34. Conditional Expressions

Item

Overview

IF

If/else construct


Table F.35. Aggregate Functions

Item

Overview

ANY_VALUE

For compatibility with MySQL mode non ONLY_FULL_GROUP_BY syntax behavior of group by in by mode

GROUP_CONCAT

Returns a concatenated, delimited list of string values.


Table F.36. System Information Functions and Operators

Item

Overview

DATABASE

Synonym for current_schema()


Table F.37. Miscellaneous Functions

Item

Overview

BIN_TO_UUID

Convert binary UUID to string

COMPRESS

Compress string and return binary data

UNCOMPRESS

Decompress the binary data compressed by compress() and restore the original string

UUID_TO_BIN

Convert string UUID to binary


Table F.38. Operators

Item

Overview

IFNULL

The corresponding value is returned according to whether the parameter is empty

ISNULL

Test whether the argument is NULL


F.52.2. SQL Functions Reference

F.52.2.1. Mathematical Functions

The following mathematical functions are supported:

  • CONV

  • LOG10

  • LOG2

  • RAND

  • TRUNCATE

F.52.2.1.1. CONV

Description

Convert numbers between different number bases.

Syntax 

conv( p_num text, p_from_base int4, p_to_base int4) RETURNS text

General rules

  • Converts numbers between different number bases.

  • Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string.

  • The minimum base is 2 and the maximum base is 36. If from_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.

Example

In the following example, '123456' converted from base 10 to base 16 is 1E240. 

select conv('123456', 10, 16);
conv  
-------
1E240
(1 row)
F.52.2.1.2. LOG10

Description

return the base-10 logarithm of the argument.

Syntax 

log10(p1 numeric) RETURNS numeric
log10(p1 double precision) RETURNS double precision

General rules

  • Returns the base-10 logarithm of p1. If X is less than or equal to 0.0E0, the function returns NULL and a warning “Invalid argument for logarithm” is reported.

Example

In the following example, return the base-10 logarithm of the argument 2. 

select log10(2);
      log10        
--------------------
0.3010299956639812
(1 row)
F.52.2.1.3. LOG2

Description

Return the base-2 logarithm of the argument.

Syntax 

log2(p_num numeric) RETURNS numeric

General rules

  • Returns the base-2 logarithm of p_num. If p_num is less than or equal to 0.0E0, the function returns NULL and a warning “Invalid argument for logarithm” is reported.

Example

In the following example, return the base-2 logarithm of the argument 64. 

select log2(64);
        log2        
--------------------
6.0000000000000000
(1 row)
F.52.2.1.4. RAND

Description

Returns a random floating-point value v in the range 0 and 1.0.

Syntax 

rand(p_seed int default null) RETURNS double precision

General rules

  • With a constant initializer argument, the seed is initialized once when the statement is prepared, prior to execution.

  • With a nonconstant initializer argument (such as a column name), the seed is initialized with the value for each invocation of RAND().

  • One implication of this behavior is that for equal argument values, RAND(N) returns the same value each time, and thus produces a repeatable sequence of column values.

  • RAND() in a WHERE clause is evaluated for every row (when selecting from one table) or combination of rows (when selecting from a multiple-table join).

Example

In the following example, the sequence of values produced by RAND(123) is the same both places it occurs. 

select rand(123);
        rand
---------------------
 0.05425240159967615
(1 row)
F.52.2.1.5. TRUNCATE

Description

Truncate to specified number of decimal places.

Syntax 

TRUNCATE(SMALLINT X,INTEGER D) returns INTEGER
TRUNCATE(INTEGER X,INTEGER D) returns INTEGER
TRUNCATE(BIGINT X,INTEGER D) returns BIGINT
TRUNCATE(DOUBLE PRECISION X,INTEGER D) returns DOUBLE PRECISION
TRUNCATE(NUMERIC,INTEGER D) returns NUMERIC

General rules

  • Returns the number X, truncated to D decimal places.

  • If D is 0, the result has no decimal point or fractional part.

  • D can be negative to cause D digits left of the decimal point of the value X to become zero.

Example

In the following example, the result of "1.223" truncated to 1 decimal places is returned. 

SELECT TRUNCATE(1.223,1);
  truncate
----------
      1.2
(1 row)

F.52.2.2. String Functions

The following string functions are supported:

  • ELT

  • FIELD

  • FIND_IN_SET

  • INSTR

  • FROM_BASE64

  • INSERT

  • LCASE

  • LOCATE

  • MID

  • SPACE

  • STRCMP

  • TO_BASE64

  • UCASE

F.52.2.2.1. ELT

Description

Return string at index number.

Syntax 

elt(str_pos int, VARIADIC strlist text[]) RETURNS text

General rules

  • ELT() returns the str_posth element of the list of strings: str1 if str_pos = 1, str2 if str_pos = 2, and so on.

  • Returns NULL if N is less than 1 or greater than the number of arguments.

  • ELT() is the complement of FIELD().

Example

In the following example,Return 'Bb' at 2. 

select elt( 2, 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
elt
-----
Bb
(1 row)
F.52.2.2.2. FIELD

Description

Index (position) of first argument in subsequent arguments.

Syntax 

field(str text, VARIADIC strlist text[]) RETURNS bigint
field(str numeric, VARIADIC strlist numeric[]) RETURNS bigint

General rules

  • Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

  • If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.

  • If str is NULL, the return value is 0 because NULL fails equality comparison with any value. FIELD() is the complement of ELT().

Example

In the following example,Index (position) of 'Bb' in 'Aa', 'Bb', 'Cc', 'Dd', 'Ff' 2 is returned. 

select field('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
field
-------
    2
(1 row)
F.52.2.2.3. FIND_IN_SET

Description

Index (position) of first argument within second argument.

Syntax 

FIND_IN_SET(str "any",strlist TEXT) returns INTEGER

General rules

  • Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. strlist is a string composed of substrings separated by , characters.

  • Returns 0 if str is not in strlist or if strlist is the empty string.

  • Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (,) character.

  • If first argument is not String Types,the first parameter will be converted to string.In other words,find_in_set(n,strlist) is equivalent to find_in_set(n::text,strlist)

  • The data type of the return value is integer.

Example

In the following example,position of 'b' within 'a,b,c,d' is returned. 

SELECT FIND_IN_SET('b','a,b,c,d');
  find_in_set
-------------
            2
(1 row)
F.52.2.2.4. FROM_BASE64

Description

Decode base64 encoded string and return result.

Syntax 

from_base64(p_str text) RETURNS text

General rules

  • Takes a string encoded with the base-64 encoded rules used by TO_BASE64() and returns the decoded result as a binary string.

  • The result is NULL if the argument is NULL or not a valid base-64 string.

  • See the description of TO_BASE64() for details about the encoding and decoding rules.

Example

In the following example,Decode base64 encoded string 'YWJj' and return result 'abc'. 

select from_base64('YWJj');
from_base64
-------------
abc
(1 row)
F.52.2.2.5. INSERT

Description

Insert substring at specified position up to specified number of characters.

Syntax 

insert(p_source text, p_pos bigint, p_len bigint, p_replacement text) RETURNS text

General rules

  • Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr.

  • Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string.

  • Returns NULL if any argument is NULL.

Example

In the following example,Insert substring 'What' at specified position 3 up to specified number 4 of characters. 

select insert('Quadratic', 3, 4, 'What');
  insert
-----------
QuWhattic
(1 row)
F.52.2.2.6. INSTR

Description

Get the first occurrence position of the substring.

Syntax 

instr(str text, patt text) RETURNS int

General rules

  • Get the first occurrence position of sub string patt in str, starting from 1.

  • If not found, return 0.

Example

In the following example, the first occurrence of substring '4de' in string 'abc124deff4de' is 6. 

select instr('abc124deff4de', '4de');
 instr 
-------
     6
(1 row)
F.52.2.2.7. LCASE

Description

Synonym for LOWER().

Syntax 

lcase(p_source text) RETURNS text

General rules

  • LCASE() is a synonym for LOWER().

  • Returns the string str with all characters changed to lowercase according to the current character set mapping. The default is utf8mb4.

Example

In the following example,Returns the string 'quadratic' with all characters changed to lowercase. 

select lcase('QuadRatic');
  lcase
-----------
quadratic
(1 row)
F.52.2.2.8. LOCATE

Description

Return the position of the first occurrence of substring.

Syntax 

LOCATE(substr TEXT,str TEXT) returns INTEGER
LOCATE(substr TEXT,str TEXT,POS INTEGER) returns INTEGER

General rules

  • The first syntax returns the position of the first occurrence of substring substr in string str.

  • The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos.

  • Returns 0 if substr is not in str.

  • Returns NULL if any argument is NULL.

Example

In the following example,position of 'bar' in 'foobarbar', starting at position 5. is returned. 

SELECT LOCATE('bar', 'foobarbar', 5);
  locate 
--------
      7
(1 row)
F.52.2.2.9. MID

Description

Return a substring starting from the specified position.

Syntax 

mid(p_source text, p_pos bigint, p_len bigint) RETURNS text

General rules

  • The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos.

  • It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function. A value of 0 for pos returns an empty string.

Example

In the following example, return a substring 'ongy' starting from the specified position 2. 

select mid('Hongye', 2, 4);
mid
------
ongy
(1 row)
F.52.2.2.10. SPACE

Description

Return a string of the specified number of spaces.

Syntax 

space(p_num int) RETURNS text

General rules

  • Returns a string consisting of N space characters.

Example

In the following example, return a string of the specified number of spaces. 

select concat('|', space(11), '|');
    concat     
---------------
|           |
(1 row)
F.52.2.2.11. STRCMP

Description

Compare two strings.

Syntax 

strcmp(p1 text, p2 text) RETURNS int

General rules

  • STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.

Example

In the following example, returns -1 the first argument is smaller than the second according to the current sort order. 

select strcmp('text', 'text2');
strcmp 
--------
    -1
(1 row)
F.52.2.2.12. TO_BASE64

Description

Return the argument converted to a base-64 string.

Syntax 

to_base64(p_str text) RETURNS text

General rules

  • Converts the string argument to base-64 encoded form and returns the result as a character string with the connection character set and collation.

  • If the argument is not a string, it is converted to a string before conversion takes place. The result is NULL if the argument is NULL.

  • Base-64 encoded strings can be decoded using the FROM_BASE64() function.

Example

In the following example, return the argument 'abc' converted to a base-64 string 'YWJj'. 

select to_base64('abc');
to_base64 
-----------
YWJj
(1 row)
F.52.2.2.13. UCASE

Description

Synonym for UPPER().

Syntax 

ucase(p_source text) RETURNS text

General rules

  • Returns the string str with all characters changed to uppercase according to the current character set mapping. The default is utf8mb4.

Example

In the following example, returns the string 'QUADRATIC' with all characters changed to uppercase according to the current character set mapping. 

select ucase('QuadRatic');
  ucase   
-----------
QUADRATIC
(1 row)

F.52.2.3. Date/Time Functions

The following date/time functions are supported:

  • DATEDIFF

  • DATE_ADD

  • DATE_FORMAT

  • DATE_SUB

  • DAY

  • DAYOFWEEK

  • FROM_UNIXTIME

  • STR_TO_DATE

  • SEC_TO_TIME

  • TIME_TO_SEC

  • TIMEDIFF

  • TIMESTAMPDIFF

  • TO_DAYS

  • TO_SECONDS

  • UNIX_TIMESTAMP

  • WEEKOFYEAR

  • YEAR

F.52.2.3.1. DATEDIFF

Description

Subtract two dates.

Syntax 

DATEDIFF(expr1 timestamp,expr2 timestamp)
DATEDIFF(expr1 text,expr2 text)

General rules

  • expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

  • Returns NULL if any argument is NULL.

Example

In the following example, 1 is returned because '2007-12-31' minus '2007-12-30' equals 1 

SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
datediff 
----------
 1
(1 row)
F.52.2.3.2. DATE_ADD

Description

Date addition.

Syntax 

DATE_ADD(expr1 timestamptz, expr2 interval) RETURN date
DATE_ADD(expr1 text, expr2 interval) RETURN date

General rules

  • Returns the date type after adding interval (year / month / day) to the expr1 date parameter

  • The second parameter expr2 only support interval (year / month / day)

Example

In the following example, '2020-01-02' is returned because '20200101' add 1 day 

select date_add('20200101', interval 1 day);
  date_add  
------------
2020-01-02
(1 row)
F.52.2.3.3. DATE_FORMAT

Description

Date format display.

Syntax 

DATE_FORMAT(expr1 timestamp, expr2 text) RETURN text
DATE_FORMAT(expr1 timestamptz, expr2 text) RETURN text
DATE_FORMAT(expr1 text, expr2 text) RETURN text

General rules

  • Return expr1 time parameter, display and output in expr2 format

Example

In the following example, '2021/12/01 11,59,59' is returned because expr2is in '%Y/%m/%d %h,%i,%s' format 

select date_format('20211201 11:59:59','%Y/%m/%d %h,%i,%s');
      date_format     
---------------------
  2021/12/01 11,59,59
(1 row)

Table F.39 shows the template patterns available for formatting date_format values.

Table F.39. Template Patterns for date_format Formatting

PatternDescription
%aAbbreviated week name
%bAbbreviated month name
%cMonth, value
%DDay of the month with English prefix
%dDay of month, value (00-31)
%eDay of month, value (0-31)
%fMicrosecond
%HHours (00-23)
%hHours (01-12)
%IHours (01-12)
%iMinute, value (00-59)
%jDay of year (001-366)
%kHours (0-23)
%lHours (1-12)
%MMonth Name
%mMonth, value (00-12)
%pAM or PM
%rTime, 12 hours (hh: mm: ss AM or PM)
%SSeconds (00-59)
%sSeconds (00-59)
%TTime, 24 hours (hh: mm: ss)
%WWeek Name
%YYear, 4 digits
%yYear, 2 digits

F.52.2.3.4. DATE_SUB

Description

Date subtraction.

Syntax 

DATE_SUB(expr1 timestamptz, expr2 interval) RETURN date
DATE_SUB(expr1 text, expr2 interval) RETURN date

General rules

  • Returns the date type after the expr1 date parameter minus the interval (year / month / day)

  • The second parameter expr2 only support interval (year / month / day)

Example

In the following example, '2019-12-31' is returned because '20200101' minus 1 day 

select date_sub('20200101', interval 1 day);
  date_sub  
------------
2019-12-31
(1 row)
F.52.2.3.5. DAY

Description

Return month date.

Syntax 

DAY(expr1 text) RETURN int2
DAY(expr1 timestamptz) RETURN int4

General rules

  • Returns the date part of a given expr1 date month

  • Only month and day parameters are not supported, such as' 0101 ', 0704

Example

In the following example, '1' is returned because 2011/12/01's date is 1 

select day('2011/12/01');
day 
-----
  1
(1 row)
F.52.2.3.6. DAYOFWEEK

Description

Return to the day of the week.

Syntax 

DAYOFWEEK(expr1 text) RETURN int4
DAYOFWEEK(expr1 timestamptz) RETURN int4

General rules

  • Returns the day of the week according to the expr1 date parameter. The return value is 1 ~ 7, and Sunday is 1

  • At least include the complete time format, such as 20200202, 200202. Parameters less than 6 bits are not supported

Example

In the following example, '4' is returned because 20200101 is Wednesday 

select dayofweek(20200101);
  dayofweek 
-----------
          4
(1 row)
F.52.2.3.7. FROM_UNIXTIME

Description

Return time format according to UNIX timestamp.

Syntax 

FROM_UNIXTIME(expr1 text) RETURN timestamp
FROM_UNIXTIME(expr1 int8) RETURN timestamp
FROM_UNIXTIME(expr1 text, expr2 text) RETURN text
FROM_UNIXTIME(expr1 int8, expr2 text) RETURN text

General rules

  • Return time format according to expr1 parameter UNIX timestamp. The date format can be specified according to the expr2 parameter

Example

In the following example, timestamp is returned according to UNIX timestamp 20220910 

select from_unixtime(20220910);
    from_unixtime    
---------------------
  1970-08-23 08:55:10
(1 row)
select from_unixtime(20220910, '%Y/%m/%d %h,%i,%s');
    from_unixtime    
---------------------
  1970/08/23 08,55,10
(1 row)

Table F.40 shows the template patterns available for formatting from_unixtime values.

Table F.40. Template Patterns for from_unixtime Formatting

PatternDescription
%aAbbreviated week name
%bAbbreviated month name
%cMonth, value
%DDay of the month with English prefix
%dDay of month, value (00-31)
%eDay of month, value (0-31)
%fMicrosecond
%HHours (00-23)
%hHours (01-12)
%IHours (01-12)
%iMinute, value (00-59)
%jDay of year (001-366)
%kHours (0-23)
%lHours (1-12)
%MMonth Name
%mMonth, value (00-12)
%pAM or PM
%rTime, 12 hours (hh: mm: ss AM or PM)
%SSeconds (00-59)
%sSeconds (00-59)
%TTime, 24 hours (hh: mm: ss)
%WWeek Name
%YYear, 4 digits
%yYear, 2 digits

F.52.2.3.8. SEC_TO_TIME

Description

Converts seconds to 'hh:mm:ss' format.

Syntax 

sec_to_time(p_secs int) RETURNS interval

General rules

  • Returns the seconds argument, converted to hours, minutes, and seconds, as a TIME value.

  • The range of the result is constrained to that of the TIME data type. A warning occurs if the argument corresponds to a value outside that range.

Example

In the following example, Converts seconds 80580 to 'hh:mm:ss' format 22:23:00. 

select mysql.sec_to_time(80580);
sec_to_time
-------------
22:23:00
(1 row)
F.52.2.3.9. STR_TO_DATE

Description

Convert string to time value.

Syntax 

STR_TO_DATE(expr1 text, expr2 text) RETURN timestamp

General rules

  • Convert string expr1 parameter and format expr2 parameter to time value

  • This function only supports returning timestamp

Example

In the following example, '2022-09-10 11:13:13' is returned according to timestamp '2022/09/10 11,13,13' and format '%Y/%m/%d %h,%i,%s' 

select str_to_date('2022/09/10 11,13,13', '%Y/%m/%d %h,%i,%s');
      str_to_date     
---------------------
  2022-09-10 11:13:13
(1 row)
select str_to_date('2022/09/10', '%Y/%m/%d');
      str_to_date     
---------------------
  2022-09-10 00:00:00
(1 row)

Table F.41 shows the template patterns available for formatting str_to_date values.

Table F.41. Template Patterns for str_to_date Formatting

PatternDescription
%aAbbreviated week name
%bAbbreviated month name
%cMonth, value
%DDay of the month with English prefix
%dDay of month, value (00-31)
%eDay of month, value (0-31)
%fMicrosecond
%HHours (00-23)
%hHours (01-12)
%IHours (01-12)
%iMinute, value (00-59)
%jDay of year (001-366)
%kHours (0-23)
%lHours (1-12)
%MMonth Name
%mMonth, value (00-12)
%pAM or PM
%rTime, 12 hours (hh: mm: ss AM or PM)
%SSeconds (00-59)
%sSeconds (00-59)
%TTime, 24 hours (hh: mm: ss)
%WWeek Name
%YYear, 4 digits
%yYear, 2 digits

F.52.2.3.10. TIME_TO_SEC

Description

Return the argument converted to seconds.

Syntax 

time_to_sec(p_time time) RETURNS int

General rules

  • Returns the time argument, converted to seconds.

Example

In the following example, returns the time argument '22:23:00' converted to seconds 80580. 

select time_to_sec('22:23:00');
time_to_sec 
-------------
      80580
(1 row)
F.52.2.3.11. TIMEDIFF

Description

Subtract time.

Syntax 

timediff(p_date1 timestamptz, p_date2 timestamptz) RETURNS interval

General rules

  • TIMEDIFF() returns expr1 − expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.

  • The result returned by TIMEDIFF() is limited to the range allowed for TIME values. Alternatively, you can use either of the functions TIMESTAMPDIFF() and UNIX_TIMESTAMP(), both of which return integers.

Example

In the following example, returns '2000-01-01 00:00:00' − '2000-01-01 00:00:00.000001' expressed as a time value. 

      select mysql.timediff('2000-01-01 00:00:00', '2000-01-01 00:00:00.000001');
          timediff
      ------------------
      -00:00:00.000001
      (1 row)
F.52.2.3.12. TIMESTAMPDIFF

Description

Subtract an interval from a datetime expression.

Syntax 

timestampdiff( p_unit text, p_ts1  timestamptz, p_ts2  timestamptz) RETURNS bigint

General rules

  • Returns datetime_expr2 − datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions.

  • One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary.

  • The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function.

Example

In the following example, Returns '1911-11-11 11:23:45.123456' − '2021-12-12 12:12:12.654321'::timestamp, the result (an integer) is given by MICROSECOND. 

select timestampdiff ('MICROSECOND', '1911-11-11 11:23:45.123456'::timestamp, '2021-12-12 12:12:12.654321'::timestamp);
  timestampdiff   
------------------
3474060507530865
(1 row)
F.52.2.3.13. TO_DAYS

Description

Return the date argument converted to days.

Syntax 

to_days(p_date timestamp) RETURNS bigint
to_days(p_date timestamptz) RETURNS bigint
to_days( p_date text) RETURNS bigint
to_days( p_date text, p_format text) RETURNS bigint
to_days(p_date bigint) RETURNS bigint

General rules

  • Given a date date, returns a day number (the number of days since year 0).

  • TO_DAYS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it does not take into account the days that were lost when the calendar was changed. For dates before 1582 (and possibly a later year in other locales), results from this function are not reliable.

Example

In the following example, return the date argument '1111-11-11' converted to days 406098. 

select to_days('1111-11-11');
to_days 
---------
  406098
(1 row)
F.52.2.3.14. TO_SECONDS

Description

Return the date or datetime argument converted to seconds since Year 0.

Syntax 

to_seconds(p_date timestamp) RETURNS bigint
to_seconds(p_date timestamptz) RETURNS bigint

General rules

  • Given a date or datetime expr, returns the number of seconds since the year 0. If expr is not a valid date or datetime value, returns NULL.

  • Like TO_DAYS(), TO_SECONDS() is not intended for use with values that precede the advent of the Gregorian calendar (1582), because it does not take into account the days that were lost when the calendar was changed. For dates before 1582 (and possibly a later year in other locales), results from this function are not reliable.

Example

In the following example, Return the date or datetime argument '2009-11-29' converted to seconds 63426672000 since Year 0. 

select to_seconds('2009-11-29'::timestamp);
to_seconds  
-------------
63426672000
(1 row)
F.52.2.3.15. UNIX_TIMESTAMP

Description

Return UNIX timestamp according to time format.

Syntax 

UNIX_TIMESTAMP() RETURN int8
UNIX_TIMESTAMP(expr1 timestamptz) RETURN int8
UNIX_TIMESTAMP(expr1 text) RETURN int8

General rules

  • Returns the UNIX timestamp according to the expr1 parameter time format. If there is no parameter, returns the current UNIX timestamp

  • Expr1 parameter contains at least the complete log format, such as 20200101

Example

In the following example, Return the unix timestamp '1577808000' corresponding parameter 20200101. 

select unix_timestamp(20200101);
  unix_timestamp 
----------------
      1577808000
(1 row)
select unix_timestamp();
  unix_timestamp 
----------------
      1663051334
(1 row)
F.52.2.3.16. WEEKOFYEAR

Description

According to the time, return to the week within a year.

Syntax 

WEEKOFYEAR(expr1 timestamptz) RETURN int4
WEEKOFYEAR(expr1 text) RETURN int4

General rules

  • According to the time expr1 parameter, return to the week within a year

  • At least include the complete time format, such as 20200202, 200202. Parameters less than 6 bits are not supported

Example

In the following example, '36' is returned because '20220910' is the 36th week of the year 

select weekofyear('20220910');
  weekofyear 
------------
          36
(1 row)
F.52.2.3.17. YEAR

Description

Returns the year of the date.

Syntax 

YEAR(expr1 timestamptz) RETURN int4
YEAR(expr1 text) RETURN int4

General rules

  • Returns the year according to the expr1 parameter date

  • It supports complete month, day and month time parameters, such as' 20200101 'and' 200101 '. It does not support only including month and day parameters, such as' 0101' and '0704'

Example

In the following example, '2020' is returned because the year of '20200101' is 2020 

select year(20200101);
  year 
------
  2020
(1 row)
select year('200101');
  year 
------
  2020
(1 row)

F.52.2.4. Conditional Expressions

The following functions for making comparisons are supported:

  • IF

F.52.2.4.1. IF

Description

If expr1 is TRUE, IF() returns expr2. Otherwise, it returns expr3.

Syntax 

IF(expr1,expr2,expr3)

General rules

  • If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is "text".

  • If one of expr2 and expr3 is a string, the result type of the IF() function is "text", otherwise it is "numeric".

  • The following data types can be used in expr2 and expr3:

    • CHAR

    • VARCHAR

    • TEXT

    • INTEGER

    • BIGINT

    • SMALLINT

    • NUMERIC

    • REAL

    • DOUBLE PRECISION

    • BOOLEAN

    • TIMESTAMPTZ

Example

In the following example, 3 is returned because the value of 1 > 2 is false. 

SELECT IF(1>2,2,3);
  if
----
  3
(1 row)

F.52.2.5. Aggregate Functions

The following aggregation functions are supported:

  • ANY_VALUE

  • GROUP_CONCAT

F.52.2.5.1. ANY_VALUE

Description

For compatibility with MySQL mode non ONLY_FULL_GROUP_BY syntax behavior of group by in by mode.

Syntax 

ANY_VALUE(anyelement)

General rules

  • This function is useful for in MySQL compatible GROUP BY queries when the ONLY_FULL_GROUP_BY SQL mode is enabled, for cases when MySQL rejects a query that you know is valid for reasons that MySQL cannot determine, Default ANY_VALUE will return the first line causing ambiguity. The function return value and type are the same as the return value and type of its argument.

    Note that this behavior is only a nonstandard syntax compatible with MySQL. It is used to ensure that the execution in LightDB will not report errors, but the results cannot be guaranteed to be completely consistent with MySQL. It is not recommended to use this feature. Please use the standard syntax format.

Example

In the following example, if id is a nonindexed column, the following query fails: 

select co1, co2, max(co3) from t1 group by id;
ERROR:  column "t1.co1" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select co1, co2, max(co3) from t1 group by id;
              ^

Use ANY_VALUE() to refer to co1 and co2: 

select any_value(co1) as co1, any_value(co2) as co2, max(co3) from t1 group by id;
co1 | co2 | max 
-----+-----+-----
  6 |   9 |  30
  4 |   5 |   5
  3 |   3 |   4
  2 |   3 |   4
(4 rows)
F.52.2.5.2. GROUP_CONCAT

Description

Returns a concatenated, delimited list of string values.

Syntax 

GROUP_CONCAT( [DISTINCT] column_name1 [ORDER BY {unsigned_integer | column_name2 | expr} [ASC | DESC] [SEPARATOR delimiter TEXT]) returns TEXT

General rules

  • This function returns a string result with the concatenated non-NULL values from a group.

  • It returns NULL if there are no non-NULL values.

  • To sort values in the result, use the ORDER BY clause.To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. The default is ascending order; this may be specified explicitly using the ASC keyword.

  • To eliminate duplicate values, use the DISTINCT clause.

  • The default separator between values in a group is comma (,). To specify a separator explicitly, use SEPARATOR followed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specify SEPARATOR ''.

Example

In the following example, the result with values of column col1 in table t delimited by ':' is returned. 

select GROUP_CONCAT(col1 separator ':') from (values('BBBB',1),('AAAA',1),('CCCC',2)) as t(col1 ,col2) group by col2;
group_concat
--------------
BBBB:AAAA
CCCC
(2 rows)

F.52.2.6. System Information Functions and Operators

The following System Information functions are supported:

  • DATABASE

F.52.2.6.1. DATABASE

Description

Synonym for current_schema(). The concept of schema is similar to that of database in MySQL. You can specify the current schema by setting the search_path.

Syntax 

DATABASE() returns TEXT

General rules

  • Returns the name of the schema that is first in the search path (or a null value if the search path is empty).

  • Like mysql,This is the schema that will be used for any tables or other named objects that are created without specifying a target schema.

Example

In the following example, current schema is lightdb. 

select database();
  database 
----------
  lighdb
(1 row)

F.52.2.7. Miscellaneous Functions

The following Miscellaneous Functions are supported:

  • BIN_TO_UUID

  • COMPRESS

  • UNCOMPRESS

  • UUID_TO_BIN

F.52.2.7.1. BIN_TO_UUID

Description

Convert binary UUID to string.

Syntax 

bin_to_uuid(p_uuid bytea, swap_flag int4 default 0) RETURNS uuid

General rules

  • BIN_TO_UUID() is the inverse of UUID_TO_BIN(). It converts a binary UUID to a string UUID and returns the result. The binary value should be a UUID as a VARBINARY(16) value.

  • The return value is a utf8 string of five hexadecimal numbers separated by dashes. (For details about this format, see the UUID() function description.) If the UUID argument is NULL, the return value is NULL. If any argument is invalid, an error occurs.

Example

In the following example, Convert binary UUID '\x0768a47dd355372b4926ee668b6cb443' to string '0768a47d-d355-372b-4926-ee668b6cb443'. 

select bin_to_uuid('\x0768a47dd355372b4926ee668b6cb443');
            bin_to_uuid              
--------------------------------------
0768a47d-d355-372b-4926-ee668b6cb443
(1 row)
F.52.2.7.2. COMPRESS

Description

Compress string and return binary data.

Syntax 

COMPRESS(expr1 text) RETURN bytea

General rules

  • Compress string expr1 parameter and return binary data. The corresponding decompression function is UNCOMPRESS

Example

In the following example, compress 'abc123' into binary data '\x06000000616263313233'. 

select compress('abc123');
        compress        
------------------------
  \x06000000616263313233
(1 row)
F.52.2.7.3. UNCOMPRESS

Description

Decompress the binary data compressed by compress() and restore the original string.

Syntax 

UNCOMPRESS(expr1 bytea) RETURN text

General rules

  • Decompress the binary data expr1 parameter compressed by compress() and restore the original string

Example

In the following example, decompress binary data '\x06000000616263313233' into bCompress 'abc123' 

select uncompress(compress('abc123'));
  uncompress 
------------
  abc123
(1 row)
F.52.2.7.4. UUID_TO_BIN

Description

Convert string UUID to binary.

Syntax 

uuid_to_bin(p_uuid uuid, swap_flag int4 default 0) RETURNS bytea

General rules

  • Converts a string UUID to a binary UUID and returns the result. (The IS_UUID() function description lists the permitted string UUID formats.)

  • The return binary UUID is a VARBINARY(16) value. If the UUID argument is NULL, the return value is NULL. If any argument is invalid, an error occurs.

Example

In the following example, Convert string UUID '0768a47d-d355-372b-4926-ee668b6cb443' to binary '\x0768a47dd355372b4926ee668b6cb443'. 

select uuid_to_bin('0768a47d-d355-372b-4926-ee668b6cb443'::uuid)::text;
            uuid_to_bin             
------------------------------------
\x0768a47dd355372b4926ee668b6cb443
(1 row)

F.52.2.8. Operators

The following Operators are supported:

  • IFNULL

  • ISNULL

F.52.2.8.1. IFNULL

Description

The corresponding value is returned according to whether the parameter is empty.

Syntax 

IFNULL(expr1 anycompatible, expr2 anycompatible) RETURNS anycompatible
IFNULL(expr1 text, expr2 text) RETURNS text

General rules

  • Judge whether the first parameter is null. If it is null, the value of the second parameter will be returned. If it is not null, the value of the first parameter will be returned

  • The two input parameters must belong to the same category, and the typcategory field of pg_type can be determined

Example

In the following example, when the first parameter is null, the second parameter 15 is returned; otherwise, the first parameter 1 is returned 

select ifnull(1, 15);
  ifnull 
--------
      1
(1 row)
select ifnull(null, 15);
  ifnull 
--------
      15
(1 row)
F.52.2.8.2. ISNULL

Description

Test whether the argument is NULL

Syntax 

isnull(text) RETURNS bool
isnull(numeric) RETURNS bool
isnull(timestamptz) RETURNS bool

General rules

  • If expr is NULL, ISNULL() returns true, otherwise it returns false.

Example

In the following example, expr is not NULL, ISNULL() returns false. 

select isnull(0)::text;
isnull 
--------
false
(1 row)
Prev Up Next
F.51. ltstattuple Home F.53. mysql_fdw