English | 简体中文
Using SQL-like languages to process lightweight data in bash.
❗ DO NOT USE THIS PROGRAM IN PRODUCTION ENVIRONMENT, because:
❕ all number types are treated as double
in this program(may loss curacy), and
❕ not performance tested, only recommended for handling lightweight data.
- cmake
- make
- g++
- pkg-config
- Lib: OpenSSL
- Lib: uuid
cd bash-sql
mkdir build && cd build
cmake -DCMAKE_INSTALL_PREFIX=/your/custom/prefix ..
make && make install
sql [OPTION] [QUERIES]
Query statements must be enclosed in single quotes.
-h
,--help
: Display this help and exit.-v
,--version
: Output version information and exit.-t
,--title
: First row is table title.-l
,--line-no
: Print line number.-i
,--interactive
: Interactive mode.-f
,--file=FILE
: Read data from FILE.-d
,--delimiter=DELIMITER
: Use DELIMITER as field delimiter.-c
,--columns=COLUMNS
: Use COLUMNS as number of columns.
Supported statements:
create: CREATE TABLE {table_name} AS {select}
insert: INSERT INTO {table_name}[column_list] {VALUES {values_list} | {select}}
update: UPDATE {table_name} SET {column}={value} [WHERE]
select: [WITH] select {columns} [FROM] [WHERE] [GROUP BY] [ORDER BY] [LIMITS]
with: WITH {table_name} AS {select}
delete: DELETE FROM {table_name} [WHERE]
drop: DROP TABLE {table_name}
describe: DESC|DESCRIBE {table_name}
show: SHOW TABLES
# non-SQL commands don't need semicolon at the end
history # show history
!n # execute history command n
Keywords and functions are case-insensitive.
The grammar is generally consistent with standard SQL
, but:
- The input data is placed in a table called
std
. - Subquery is not supported, please use
with
instead. - Group by allows only field names or field indexes(in select clauses).
ps -aux | ./sql -tlc11 'select user, pid, `%cpu`, `%mem`, command from std limit 10;'
- abs
- acos
- asin
- atan
- ceil
- ceiling
- conv
- cos
- cot
- degrees
- exp
- floor
- ln
- log
- log2
- log10
- mod
- pi
- pow
- power
- radians
- rand
- round
- sign
- sin
- sqrt
- tan
- truncate
All date and time formats are compatible with the
strftime
function.
- adddate
- addtime
- curdate
- current_date
- current_time
- current_timestamp
- curtime
- date
- datediff
- date_add
- date_format
- date_sub
- day
- dayname
- dayofmonth
- dayofweek
- dayofyear
- from_unixtime
- hour
- last_day
- localtime
- localtimestamp
- makedate
- maketime
- minute
- month
- monthname
- now
- quarter
- second
- sec_to_time
- str_to_date
- subdate
- subtime
- sysdate
- time
- timediff
- time_to_sec
- unix_timestamp
- week
- weekday
- weekofyear
- year
- yearweek
- ascii
- bin
- char
- concat
- concat_ws
- elt
- field
- hex
- insert
- instr
- lcase
- left
- length
- locate
- lower
- lpad
- ltrim
- mid
- oct
- position
- repeat
- replace
- reverse
- right
- rpad
- rtrim
- space
- strcmp
- substr
- substring
- substring_index
- trim
- ucase
- unhex
- upper
prototype: abs(
x
)
Returns the absolute value of x
, or NULL if x
is NULL.
prototype: acos(
x
)
Returns the arc cosine of x
, that is, the value whose cosine is x
.
Returns NULL if x
is not in the range -1 to 1, or if x
is NULL.
prototype: adddate(
date
,interval
,[unit]
)
ADDDATE() is a synonym for DATE_ADD().
prototype: addtime(
date
,expr
)
Adds expr
to date
. expr
is a time expression.
Returns NULL if date
or expr
are NULL.
prototype: app()
Returns the application name.
prototype: ascii(
str
)
Returns the numeric value of the leftmost character of the string str
.
Returns 0 if str
is the empty string. Returns NULL if str
is NULL.
prototype: asin(
x
)
Returns the arc sine of x
, that is, the value whose sine is x
.
Returns NULL if x
is not in the range -1 to 1, or if x
is NULL.
prototype: atan(
x
)
Returns the arc tangent of x
, that is, the value whose tangent is x
.
Returns NULL if x
is NULL.
prototype: author()
Returns the author of the application.
prototype: avg(
expr
)
Returns the average value of expr
(skip NULL value). If all expr
s are NULL, returns NULL.
prototype: bin(
n
)
Returns a string representation of the binary value of n
. Returns NULL if n
is NULL.
This is equivalent to CONV(N, 10, 2)
.
prototype: case(
when1
,then1
, ...,whenN
,thenN
,else
)
If when1
is true, returns then1
.
Otherwise, if when2
is true, returns then2
, and so on.
If none of the when
expressions are true, returns else
.
If else
is omitted, returns NULL.
prototype: ceil(
x
)
ceil() is a synonym for CEILING().
prototype: ceiling(
x
)
Returns the smallest integer value not less than x
. Returns NULL if x
is NULL.
prototype: char(
n
, ...)
Interprets each argument n
as an integer and returns a string
consisting of the characters given by the code values of those integers.
NULL values are skipped.
prototype: coalesce(
expr1
,expr2
, ...)
Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
prototype: concat(
str1
,str2
, ...)
Returns the string that results from concatenating the arguments. May have one or more arguments. Returns NULL if any argument is NULL.
prototype: concat_ws(
sep
,str1
,str2
, ...)
Concatenate with separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments.
The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.
prototype: conv(
n
,from_base
,to_base
)
Returns a string representation of the number n
, converted from base from_base
to base to_base
.
Returns NULL if any argument is NULL or n
is a invalid number.
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.
prototype: cos(
x
)
Returns the cosine of x
, where x
is given in radians. Returns NULL if x
is NULL.
prototype: cot(
x
)
Returns the cotangent of x
. Returns NULL if x
is NULL.
prototype: count(
expr
)
Returns a count of the number of non-NULL values of expr
in the rows.
prototype: curdate()
Returns the current date as a value in 'YYYY-mm-dd' format.
prototype: current_date()
CURRENT_DATE() is a synonym for CURDATE().
prototype: current_time()
CURRENT_TIME() is a synonym for CURTIME().
prototype: current_timestamp()
CURRENT_TIMESTAMP() is a synonym for NOW().
curtime()
Returns the current time as a value in 'HH:MM:SS' format.
prototype: date(
expr
)
Extracts the date part of the date or datetime expression expr
.
Returns NULL if expr
is NULL.
prototype: datediff(
expr1
,expr2
)
Returns 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.
prototype: date_add(
date
,interval
,[unit]
)
Adds an interval to a date.
The date
argument specifies the starting date or datetime value,
and the interval
argument specifies the interval value to be added
to the starting date, the unit
argument specifies the units of the interval value.
Available units are:
- 1: second
- 2: minute
- 3: hour
- 4: day, default
- 5: week
Returns NULL if either date
or interval
is NULL.
prototype: date_format(
date
,format
)
Formats the date
value according to the format
string.
If either argument is NULL, the function returns NULL.
prototype: date_sub(
date
,interval
,[unit]
)
It's the same as date_add but subtracts the interval from the date
.
prototype: day(
date
)
DAY() is a synonym for DAYOFMONTH().
prototype: dayname(
date
)
Returns the name of the weekday for date
. The language used for the name is
controlled by the value of the lc_time_names
system variable.
Returns NULL if date
is NULL.
prototype: dayofmonth(
date
)
Returns the day of the month for date
, in the range 1 to 31.
Returns NULL if date
is NULL.
prototype: dayofweek(
date
)
Returns the weekday index for date
(0 = Sunday, 1 = Monday, ... 6 = Saturday).
Returns NULL if date
is NULL.
prototype: dayofyear(
date
)
Returns the day of the year for date
, in the range 1 to 366.
Returns NULL if date
is NULL.
prototype: decode(
expr
,value1
,result1
,value2
,result2
, ..., [default
])
If expr
equals value1
, returns result1
.
Otherwise, if expr
equals value2
, returns result2
, and so on.
If expr
matches none of the values, returns default
.
If default
is omitted, returns NULL.
prototype: degrees(
x
)
Returns the argument x
, converted from radians to degrees. Returns NULL if x
is NULL.
prototype: double(
x
)
Cast x
to double. Returns 0 if not a valid number.
prototype: elt(
n
,str1
,str2
, ...)
Returns the n
th element of the list of strings:
str1
if n
= 1, str2
if n
= 2, and so on.
Returns NULL if n
is less than 1, greater than the number of arguments, or NULL.
ELT() is the complement of FIELD().
prototype: exp(
x
)
Returns the value of e (the base of natural logarithms) raised to the power of x
.
The inverse of this function is LOG() (using a single argument only) or LN().
If x
is NULL, this function returns NULL.
prototype: export(
table_name
,file_path
,[with_title]
,[with_line_no]
,[delimiter]
)
Exports a table to a CSV file.
The table_name
specifies the name of the table to export.
The file_path
specifies the path to the file to export to.
The with_title
parameter determines whether the first row of the file contains column names,
defaults to false.
The with_line_no
parameter determines whether the first column of the file contains line numbers,
defaults to false.
The delimiter
parameter determines the delimiter used in the file, defaults to ','.
prototype: field(
str
,str1
,str2
, ...)
Returns the index (position) of the str
in the subsequent string list.
Returns 0 if str
is not found.
FIELD() is the complement of ELT().
prototype: floor(
x
)
Returns the largest integer value not greater than x
. Returns NULL if x
is NULL.
prototype: from_base64(
str
)
Takes a string encoded with the base-64 encoded rules 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.
prototype: from_unixtime(
timestamp
,[format]
)
Returns a representation of unix_timestamp as a character string value
with the format given by the format
argument.
If format
is omitted, the default format is %F %T
.
prototype: get(
key
)
Returns the value of an environment variable named key
,
or empty string if the variable does not exist.
@key
is a synonym for get(key)
.
prototype: greatest(
x1
,x2
, ...)
With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST().
- If any argument is NULL, the result is NULL.
- If any argument is a string, the result is a string.
- If any argument is a real number, the result is a real number.
- If all arguments are integer-valued, the result is an integer.
Returns NULL if any argument is NULL.
prototype: group_concat(
expr
)
Returns a string result with the concatenated non-NULL values from a group. It returns NULL if any argument is NULL.
prototype: hex(
n
ors
)
This function can be used to obtain a hexadecimal representation of a decimal number or a string.
prototype: hour(
time
)
Returns the hour for time. The range of the return value is 0 to 23.
Returns NULL if time
is NULL.
prototype: if(
condition
,true_value
,false_value
)
Returns true_value
if condition
is true, otherwise returns false_value
.
prototype: ifnull(
expr1
,expr2
)
Returns expr2
if expr1
is NULL, otherwise returns expr1
.
This is the same as the COALESCE() function with two arguments.
prototype: import(
table_name
,file_path
,[with_title]
,[columns]
,[delimiter]
)
Imports data from a file into a table. The file is expected to be in CSV format.
The table_name
specifies the name of the table to import the data into.
The file_path
specifies the path to the file to import.
The with_title
parameter determines whether the first row of the file contains column names,
defaults to false.
The columns
parameter determines the column count of the file,
if omitted the column count is determined automatically.
The delimiter
parameter determines the delimiter used in the file, defaults to '\s+'.
prototype: insert(
str
,pos
,len
,newstr
)
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.
prototype: instr(
str
,substr
)
Returns the position of the first occurrence of substring substr
in string str
.
This is the same as the two-argument form of LOCATE(),
except that the order of the arguments is reversed.
prototype: isnull(
expr
)
If expr
is NULL, Returns true
, otherwise it returns false
.
prototype: int(
x
)
Cast x
to integer. Returns 0 if not a valid number.
prototype: last_day(
date
)
Takes a date or datetime value and returns the corresponding value for the last day of the month.
Returns NULL if the date
is invalid or NULL.
prototype: lcase(
str
)
LCASE() is a synonym for LOWER().
prototype: least(
x1
,x2
, ...)
With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the same rules as for GREATEST().
prototype: left(
str
,len
)
Returns the leftmost len
characters from the string str
,
or NULL if any argument is NULL.
prototype: length(
str
)
Returns the length of the string str. Returns NULL if str is NULL.
prototype: ln(
x
)
Returns the natural logarithm of x
; that is, the base-e logarithm of x
.
If x
is less than or equal to 0, returns NULL.
Returns NULL if x
is NULL.
This function is synonymous with one-argument form of LOG().
The inverse of this function is EXP().
prototype: localtime()
LOCALTIME() is a synonym for NOW().
prototype: localtimestamp()
LOCALTIMESTAMP() is a synonym for NOW().
prototype: locate(
substr
,str
,[pos]
)
Returns the position of the first occurrence of substring substr
in string str
,
starting at position pos
, when pos
is omitted, it starts at position 1.
Returns 0 if substr
is not in str
. Returns NULL if any argument is NULL.
prototype: log(
[b]
,x
)
If called with one parameter, this function returns the natural logarithm of x
.
If x
is less than or equal to 0, returns NULL.
Returns NULL if x
or b
is NULL.
The inverse of this function (when called with a single argument) is the EXP().
If called with two parameters, this function returns the logarithm of x
to the base b
.
If x
is less than or equal to 0, or if b
is less than or equal to 1, then NULL is returned.
LOG(b, x)
is equivalent to LOG(x)/LOG(b)
.
prototype: log2(
x
)
Returns the base-2 logarithm of x
. If x
is less than or equal to 0, returns NULL.
Returns NULL if x
is NULL.
This function is equivalent to the expression LOG(x)/LOG(2)
.
prototype: log10(
x
)
Returns the base-10 logarithm of x
. If x
is less than or equal to 0, returns NULL.
Returns NULL if x
is NULL.
This function is equivalent to LOG(10, x)
.
prototype: lower(
str
)
Returns the string str
with all characters changed to lowercase, or NULL if str
is NULL.
prototype: lpad(
str
,len
,padstr
)
Returns the string str
, left-padded with the string padstr
to a length of len
characters.
If str
is longer than len
, the return value is shortened to len
characters.
Returns NULL if any argument is NULL.
prototype: ltrim(
str
)
Returns the string str
with leading space characters removed.
Returns NULL if str
is NULL.
prototype: makedate(
year
,dayofyear
)
Returns a date, given year
and dayofyear
values.
dayofyear
must be greater than 0 or the result is NULL.
The result is also NULL if either argument is NULL.
prototype: maketime(
hour
,minute
,second
)
Returns a time value calculated from the hour
, minute
, and second
arguments.
Returns NULL if any argument is NULL.
prototype: max(
expr
)
Returns the maximum value of expr
.
MAX() may take a string argument; in such cases, it returns the maximum string value.
prototype: md5(
str
)
Calculates an MD5 128-bit checksum for the str
.
The value is returned as a string of 32 hexadecimal digits,
or NULL if the argument was NULL.
prototype: mid(
str
,pos
,[len]
)
MID() is a synonym for SUBSTRING().
prototype: min(
expr
)
Returns the minimum value of expr
.
MIN() may take a string argument; in such cases, it returns the minimum string value.
prototype: minute(
time
)
Returns the minute for time
, in the range 0 to 59, or NULL if time is NULL.
prototype: mod(
n
,m
)
Modulo operation. Returns the remainder of n
divided by m
. Returns NULL if m
or n
is NULL.
MOD(n, 0)
returns NULL.
prototype: month(
date
)
Returns the month for date
, in the range 1 to 12 for January to December.
Returns NULL if date
is NULL.
prototype: monthname(
date
)
Returns the full name of the month for date
. The language used for the name is
controlled by the value of the lc_time_names
system variable.
Returns NULL if date
is NULL.
prototype: now()
Returns the current date and time as a value in 'YYYY-mm-dd HH:MM:SS' format.
prototype: nullif(
expr1
,expr2
)
Returns NULL if expr1
equals expr2
; otherwise returns expr1
.
This is the same as IF(expr1 = expr2, NULL, expr1)
.
prototype: oct(
n
)
Returns a string representation of the octal value of n
.
This is equivalent to CONV(N, 10, 8)
. Returns NULL if n
is NULL.
prototype: pi()
Returns the value of π
(pi).
prototype: position(
substr
,str
)
This is the same as the two-argument form of LOCATE()
prototype: pow(
x
,y
)
Returns the value of x
raised to the power of y
. Returns NULL if x
or y
is NULL.
prototype: power(
x
,y
)
This is a synonym for POW().
prototype: quarter(
date
)
Returns the quarter of the year for date
,
in the range 1 to 4, or NULL if date
is NULL.
prototype: radians(
x
)
Returns the argument x
, converted from degrees to radians. (Note that π
radians equals 180 degrees.)
Returns NULL if x
is NULL.
prototype: rand(
[n]
)
Returns a random floating-point value v
in the range 0 <= v
< 1.0.
If an integer argument n
is specified, it is used as the seed value.
For equal argument values, RAND
(n
) returns the same value each time.
prototype: repeat(
str
,count
)
Returns a string consisting of the string str
repeated count
times.
If count
is less than 1, returns an empty string.
Returns NULL if str
or count
is NULL.
prototype: replace(
str
,from
,to
)
Returns the string str
with
all occurrences of the string from
replaced by the string to
.
This function is case-insensitive.
Returns NULL if any argument is NULL.
prototype: reverse(
str
)
Returns the string str
with the order of the characters reversed,
or NULL if str
is NULL.
prototype: right(
str
,len
)
Returns the rightmost len
characters from the string str
,
or NULL if any argument is NULL.
prototype: round(
x
,[d]
)
Rounds the argument x
to d
decimal places.
d
defaults to 0 if not specified.
d
can be negative to cause d
digits left of the decimal point of the value x
to become zero.
If x
or d
is NULL, returns NULL.
prototype: rpad(
str
,len
,padstr
)
Returns the string str
, right-padded with the string padstr
to a length of len
characters.
If str
is longer than len
, the return value is shortened to len
characters.
Returns NULL if any argument is NULL.
prototype: rtrim(
str
)
Returns the string str
with trailing space characters removed.
Returns NULL if str
is NULL.
prototype: second(
time
)
Returns the second for time
, in the range 0 to 59, or NULL if time
is NULL.
prototype: sec_to_time(
seconds
)
Returns the seconds
argument, converted to hours, minutes, and seconds.
Returns NULL if seconds
is NULL.
prototype: serial([
n
])
Returns a random serial number.
If an integer argument n
is specified, it is used as the length of the serial number,
Otherwise, the length is 8.
prototype: set(
key
,value
)
Set or update (if exists) the environment variable key
to value
.
prototype: sha(
str
)
SHA() is synonymous with SHA1().
prototype: sha1(
str
)
Calculates an SHA-1 160-bit checksum for the string. The value is returned as a string of 40 hexadecimal digits, or NULL if the argument is NULL.
prototype: sha2(
str
,[n]
)
Calculates the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512). The first argument is the plaintext string to be hashed. The second argument indicates the desired bit length of the result, which must have a value of 224, 256, 384, 512, or 0 (which is equivalent to 256). If the second argument is omitted, the default is 256.
If either argument is NULL or the hash length is not one of the permitted values, the return value is NULL. Otherwise, the function result is a hash value containing the desired number of bits.
prototype: sha224(
str
)
This function is the same as SHA2() with a hash length of 224 bits.
prototype: sha256(
str
)
This function is the same as SHA2() with a hash length of 256 bits.
prototype: sha384(
str
)
This function is the same as SHA2() with a hash length of 384 bits.
prototype: sha512(
str
)
This function is the same as SHA2() with a hash length of 512 bits.
prototype: sign(
x
)
Returns the sign of the argument as -1, 0, or 1, depending on whether x
is negative, zero, or positive.
Returns NULL if x
is NULL.
prototype: sin(
x
)
Returns the sine of x
, where x
is given in radians. Returns NULL if x
is NULL.
prototype: sleep(
milliseconds
)
Sleep(pauses) for the specified number of milliseconds. Returns 0.
Note that since the program is single-threaded, the sleep function will execute multiple times if there are multiple lines.
prototype: space(
n
)
Returns a string consisting of n
space characters, or NULL if n
is NULL.
prototype: sqrt(
x
)
Returns the square root of a non-negative number x
. If x
is NULL, the function returns NULL.
prototype: strcmp(
str1
,str2
)
Returns 0 if the strings are the same, -1 if the first argument is smaller than the second, 1 otherwise.
Returns NULL if either argument is NULL.
prototype: string(
x
)
Cast x
to string.
prototype: str_to_date(
str
,format
)
Returns the argument parsed as a date. If str
or format
is NULL, the function returns NULL.
This function is the opposite of DATE_FORMAT().
prototype: subdate(
date
,days
)
SUBDATE() is a synonym for DATE_SUB() of two-argument form.
prototype: substr(
str
,pos
,[len]
)
SUBSTR() is a synonym for SUBSTRING().
prototype: substring(
str
,pos
,[len]
)
Returns a substring len
characters long from string str
, starting at position pos
.
The first character of the string is at position 1.
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 value of 0 for pos
returns an empty string.
It returns NULL if any argument is NULL.
If len
is less than 1, the result is the empty string.
prototype: substring_index(
str
,delim
,count
)
Returns the substring from string str
before count occurrences of the delimiter delim
.
If count
is positive, everything to the left of the final delimiter (counting from the left) is returned.
If count
is negative, everything to the right of the final delimiter (counting from the right) is returned.
Returns NULL if any argument is NULL.
prototype: subtime(
date
,expr
)
It's the same as ADDTIME() but subtracts.
prototype: sum(
expr
)
Returns the sum of expr
(skip NULL value). If all expr
s are NULL, returns NULL.
prototype: sysdate()
It's a synonym for NOW().
prototype: tan(
x
)
Returns the tangent of x
, where x
is given in radians. Returns NULL if x
is NULL.
prototype: time(
expr
)
Extracts the time part of the time or datetime expression expr
and
returns it as a string. Returns NULL if expr
is NULL.
prototype: timediff(
time1
,time2
)
Returns time1
− time2
expressed as a time value.
Returns NULL if either argument is NULL.
prototype: time_to_sec(
time
)
Returns the time
argument, converted to seconds.
Returns NULL if time is NULL.
prototype: to_base64(
str
)
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.
prototype: trim(
str
)
Returns the string str
with all leading and trailing space characters removed.
Returns NULL if str
is NULL.
prototype: truncate(
x
,[d]
)
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.
If x
or d
is NULL, returns NULL.
prototype: ucase(
str
)
UCASE() is a synonym for UPPER().
prototype: unhex(
hex_string
)
Returns a string containing the character representation of the hexadecimal argument.
The characters in the argument string must be legal hexadecimal digits. If the argument contains any non-hexadecimal digits, or is itself NULL, the result is NULL.
It's the opposite of the HEX() function.
prototype: unix_timestamp(
[date]
)
Returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC.
If date
is omitted, use the current date and time.
prototype: unset(
key
)
Delete environment variable named key
.
prototype: upper(
str
)
Returns the string str
with all characters changed to uppercase, or NULL if str
is NULL.
prototype: uuid()
Returns a string that conforms to UUID version 4 as described in RFC 4122.
prototype: version()
Returns the version of the application.
prototype: week(
date
,[first_day]
,[mode]
)
This function returns the week number for date
.
The first_day
argument determines the first day of the week,
and the mode
argument determines the counting mode of the weeks.
The default values for first_day
and mode
are 1 and 3, respectively.
first_day
can be 0 (Sunday) or 1 (Monday), ... 6 (Saturday).
mode
can be one of the following values:
- 0: in range 0-53, week 0 is the week that with a
first_day
in this year. - 1: in range 0-53, week 0 is the week that with 4 or more days in this year.
- 2: in range 1-53, week 1 is the week that with a
first_day
in this year. - 3: in range 1-53, week 1 is the week that with 4 or more days in this year.
prototype: weekday(
date
)
Returns the weekday index for date
(0 = Sunday, 1 = Monday, ... 6 = Saturday).
Returns NULL if date is NULL.
prototype: weekofyear(
date
)
Returns the calendar week of the date
as a number in the range from 1 to 53.
Returns NULL if date
is NULL.
This function is equivalent to WEEK(date, 1, 3)
.
prototype: year(
date
)
Returns the year for date
.
The year is returned as a number in the range 1000 to 9999.
Returns NULL if date
is NULL.
prototype: yearweek(
date
,[first_day]
,[mode]
)
Returns the year and week for date
.
The year in the result may be different from the year in the date
argument
for the first and the last week of the year. Returns NULL if date
is NULL.
The first_day
and mode
argument works exactly like to WEEK().