The following functions have been written by students from the CIS department at Kansas State University and their instructor in the CIS308 C/C++ Language Laboratory course. They are released under an Open Source MIT license. The goal has been to provide functions that behave exactly like the target functions, even when the result isn't exactly what you would intuitively expect. For instance sqrt(-1) and sqrt('hello') return errors with most products, except with MySQL that returns, respectively, NULL and 0 (and warnings); there is no way to return warnings in SQLite, but the functions in the MySQL library return what MySQL would return. Error messages don't try to mimic the target DBMS, only to be consistent with other SQLite error messages.

In a few cases, functions differ slightly from their model: for instance the extract() function is normally called as follows:

select extract(year from current_date), ...
As this function requires special processing when parsing the SQL statement, its syntax has been changed to:
select extract('year', current_date), ...
Note that the date part must be quoted in this implementation, and that the from has disappeared and is replaced by a comma. However, passing null as the first parameter doesn't return null as is usual for functions, but a syntax error as would be the case when trying
select extract(null from current_date), ...
The same logic has been applied to a few other functions (check library details).

Authors

These libraries have been created out of code contributed by:

Casey Andres, Joshua Benard, Connor Berg, Blaine Bettencourt, Christopher Boschert, Logan Brecheisen, Brian Buchta, Dustin Chew, Austin Elliott, Daniel Elsbernd, Uzoma Emuchay, Fan Zhiang, Austin Fangman, Stéphane Faroult, David Freeman, Jonathan Gooden, Christian Hughes, Joshua Humphreys, Jordan Jacobs, Brent Johnson, Nicholas Johnson, Patrick Kliesner, Pavel Kuropatkin, Joshua Langford, Andrew Leigh, Li Yuanyang, Kevin Manase, Blake Martin, Andrew Massey, Sagar Mehta, Steven Mercier, Grant Meyerhoff, Ifeanyichukwu Otuonye, Arunothayan Paramanathan, Joseph Perkins, Corey Porubsky, Caullen Sasnett, Say Gui, Zachary Sliefert, Jeremy Taylor, Matthew Traudt, James Tyson, Phillip Urban, Daniel Wagner, George Walker, Geordy Williams, Hayden Wilson, Wong Jiawen, Hayden Woods, Zhang Kai, and another student who wished stay anonymous.

The code has been moved to GitHub. Please feel free to contribute!

MySQL functions for SQLite show Show details

Here is the list of MySQL functions that have been implemented in the library. Function names link to the MySQL docs. Differences between the original MySQL function and the library implementation are specified in comments.

Control flow functions

FunctionComments
if()

String functions

All string functions are UTF-8 aware. However, collations are ignored.

FunctionComments
ascii()
bin()
bit_length()
char_length()
char()
character_length()Synonym for char_length().
concat_ws()
concat()
elt()
export_set()
field()
find_in_set()
format()If the optional locale parameter isn't set, this function checks the LANG environment variable.
hex()
insert()You aren't normally supposed to name functions as keywords, and SQLite (logically) doesn't accept a function called insert(). Renamed strinsert() in this implementation.
instr()
lcase()Synonym for lower()
left()left is an SQL keyword (left join). Renamed strleft() in this implementation.
length()The length() function in MySQL counts bytes, not characters as does the SQLite version, which is equivalent to MySQL's char_length() function.
load_file()
locate()
lower()Although SQLite provides this function, it doesn't work properly (as of SQLite 3.8) with accented characters. This implementation tries harder; it also works with Greek and Cyrillic characters.
lpad()
make_set()
oct()
octet_length()Synonym for length().
ord()
position()Synonym for locate(). In this implementation it must be used as follows:
select position(substr, str)
instead of (MySQL original version)
select position(substr in str)
quote()Doesn't exactly behaves as the MySQL version as a \ (backslash) is considered as a regular character by SQLite, not an escape character. This implementation, though, serves the same purpose as the MySQL function, which is making data extracted from the database usable in a statement by escaping (doubling) quotes.
repeat()
replace()
reverse()
right()right is an SQL keyword (right outer join). Renamed strright() in this implementation.
rpad()
soundex()The MySQL implementation of soundex() is a bit special as it doesn't truncate codes at four characters as is customary.
space()
strcmp()
substring_index()
substring()SUBSTRING(str FROM pos FOR len) isn't supported.
trim()The MySQL syntax is TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str). This implementation accepts between 1 and 3 parameters:
  • If there is a single argument, spaces are removed at both ends from the argument.
  • If there are two arguments, the first one is understood as what should be removed at both ends from the second one.
  • If there are three arguments, the first one must be one of BOTH, LEADING, or TRAILING (in upper or lower case, but it must be quoted), the second one is understood as what should be removed at the end(s) specified by the first argument, and the third argument is the trimmed string.
ucase()Synonym for upper()
unaccent()Bonus function that doesn't exist in MySQL (nor in Oracle or SQL Server or PostgreSQL); it replaces every accented letter in the argument by its unaccented version. Note that:
  • No attempt is made to find a correct equivalent: ü is replaced with u, not with ue as, say, a German speaker might expect it.
  • The final result is not necessarily an ASCII string. The function also strips Greek characters, for instance, of their accents.
unhex()
upper()Although SQLite provides this function, it doesn't work properly (as of SQLite 3.8) with accented characters. This implementation tries harder; it also works with Greek and Cyrillic characters..

Numeric functions

FunctionComments
acos()
asin()
atan()
ceil()
ceiling()Synonym for ceil()
conv()
cos()
degrees()
exp()
floor()
ln()
log10()
log2()
log()
mod()
pi()
pow()
power()Synonym for pow()
radians()
sign()
sin()
sqrt()
tan()
truncate()

Datetime functions

The only two date formats that are always recognized are what current_date and current_timestamp return, respectively YYYY-MM-DD and YYYY-MM-DD HH:MI:SS. Some functions, though, try to convert a string of digits to a date or time in the same way as MySQL. Functions that return names (month, day) are affected by the setting of the LANG environment variable (eg if LANG is set to de_DE.utf8, names will be returned in German).

Fractional seconds and time zones are ignored. Incomplete dates (day of month or month number set to 00) aren't supported.

FunctionComments
adddate() The syntax isn't quite the same as in the original MySQL version. Where MySQL wants
SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
this implementation requires
SELECT ADDDATE('2008-01-02', 'INTERVAL 31 DAY');
Note that the whole interval expression must be quoted.
As with MySQL, you can also write
SELECT ADDDATE('2008-01-02', 31);
(DAY is the default unit), but the following syntax is also accepted by this implementation
SELECT ADDDATE('2008-01-02 08:30:00', 10, 'HOUR');
addtime()
convert_tz()Although time zones are not supported in this library, this function is. Time zones must be specified as +/-HH:MI. Named time zones are unsupported.
curdate()
current_date()Synonym for curdate()
current_time()Synonym for curtime()
current_timestamp()Synonym for now()
curtime()Always returned as HH:MI:SS, never as HHMISS as the function has no knowledge of the context
date_add()The syntax isn't quite the same as in the original MySQL version. Where MySQL wants
SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
this implementation wants
SELECT DATE_ADD('1900-01-01 00:00:00', 'INTERVAL -1 10 DAY_HOUR');
Note that the whole interval expression must be quoted.
date_sub()Syntax is similar to the date_add() syntax.
datediff()
day()Synonym for dayofmonth()
dayname()The value returned depends on the current settings of the LANG environment variable
dayofmonth()
dayofweek()
dayofyear()
extract() MySQL uses the syntax
select extract(year from current_date);
It must be written with this implementation
select extract('year', current_date);
last_day()
localtime()Synonym for now()
localtimestamp()Synonym for now()
makedate()
month()
monthname()The value returned depends on the current settings of the LANG environment variable
now()
period_diff()
quarter()
sec_to_time()
subdate()Syntax is similar to the adddate() function.
subtime()
time_to_sec()
time()
timediff()
timestampadd()The unit (first parameter) must be quoted.
timestampdiff()The unit (first parameter) must be quoted.
utc_timestamp()
weekofyear()
year()

Bit functions

FunctionComments
bit_count()

Information functions

FunctionComments
connection_id()Returns the current process id
current_user()Returns the name of the current system login appended with @localhost
database()Returns the name of the SQLite file stripped of its extension, NULL if in-memory
last_insert_id()
row_count()
schema()Synonym for database()
session_user()Synonym for current_user()
system_user()Synonym for current_user()
user()Synonym for current_user()
version()Synonym for sqlite_version()

Aggregate functions

FunctionComments
bit_and()
bit_or()
bit_xor()
std()Synonym for stddev_pop()
stddev()Synonym for stddev_pop()
stddev_pop()
stddev_samp()
var_pop()
var_samp()
variance()Synonym for var_pop()

Oracle functions for SQLite show Show details

Numeric functions

FunctionComment
acos()
asin()
atan()
atan2()
bitand()
ceil()
cos()
cosh()
exp()
floor()
ln()
log()
nanvl()The implementation of the function is somewhat more general than the Oracle one, which only returns the second argument if the first one is the special 'NaN' value. In this implementation, the first parameter is returned if it's numeric, otherwise the second one (that MUST be numeric) is returned. As with the Oracle version, NULL is returned as soon as any of the two parameters is null, even if the first parameter is numeric and the null parameter is the second one.
mod()
power()
remainder()
round(number)
sign()
sin()
sinh()
sqrt()
tan()
tanh()
trunc(number)
width_bucket()

Character functions returning character values

FunctionComment
chr()USING NCHAR_CS isn't supported.
concat()
initcap()Also works with other alphabets than the Latin one.
lower() Although SQLite provides this function, it doesn't work properly (as of SQLite 3.8) with accented characters. This implementation tries harder; it also works with Greek and Cyrillic characters.
lpad()
rpad()
soundex()
translate()TRANSLATE ... USING isn't supported.
trim()The Oracle syntax is TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str). This implementation accepts between 1 and 3 parameters:
  • If there is a single argument, spaces are removed at both ends from the argument.
  • If there are two arguments, the first one is understood as what should be removed at both ends from the second one.
  • If there are three arguments, the first one must be one of BOTH, LEADING, or TRAILING (in upper or lower case, but it must be quoted), the second one is understood as what should be removed at the end(s) specified by the first argument, and the third argument is the trimmed string.
unaccent()Bonus function that doesn't exist in Oracle (nor in MySQL or SQL Server or PostgreSQL); it replaces every accented letter in the argument by its unaccented version. Note that:
  • No attempt is made to find a correct equivalent: ü is replaced with u, not with ue as, say, a German speaker might expect it.
  • The final result is not necessarily an ASCII string. The function also strips Greek characters, for instance, of their accents.
upper() Although SQLite provides this function, it doesn't work properly (as of SQLite 3.8) with accented characters. This implementation tries harder; it also works with Greek and Cyrillic characters.

Character functions returning number values

FunctionComment
ascii()
instr()
lengthb()Length in bytes.

Datetime functions

Oracle dates range from 4712 BC to 9999; however, Oracle uses two different calendars, the Gregorian one from Oct 15th, 1582 onwards, and the Julian one before Oct 5th, 1582 (the difference between the two is that the Julian calendar has more leap years). Oracle assumes that the dates between October 4th and October 15th never were, which was only true in some countries, as the adoption of the Gregorian calendar differed by country. The functions in this library make the same assumptions as Oracle concerning October 1582, and also use the Julian calendar prior to the Gregorian calendar reform. There is another problem with year 0, which never was (year 1 immediately followed year -1). Although Oracle considers year 0 to be invalid, it says that Jan 1st of year 1 was a Saturday, but that Dec 31st of year -1 was a Wednesday, at which point the functions in this library beg to disagree and depart from the Oracle behavior. It seems more logical to have a continuity of week days if nothing else.
These calendar questions are horribly complicated, and it's rather futile to try forcing a modern Western calendar upon periods when, even in Western Europe, the most calendar-conscious people were probably still counting years "ab Urbe condita" (since the founding of Rome).

For these libraries, the default date format is YYYY-MM-DD [HH24:MI:SS], not the traditional Oracle format (sometimes changed by database administrators) DD-MON-YY.

FunctionComment
add_days()Bonus function that replaces adding (with +) a number of days to a date in Oracle. Instead of using:
SELECT CURRENT_DATE + 3, ...
to obtain the date in three days' time, you can use:
SELECT ADD_DAYS(CURRENT_DATE, 3), ...
As with Oracle, you can add non-integer values, understood as fractions of days. Beware though that if for Oracle by adding 1/24 you add one hour, in SQLite 1/24 is an integer division that returns 0, so if you want to add one hour you must add for instance 1/24.0, which will be understood as a float value.
add_months()
extract() The syntax isn't quite the same as in the original Oracle version. Where Oracle wants
SELECT EXTRACT(YEAR FROM date_column), ...
this implementation requires
SELECT EXTRACT('YEAR', date_column), ...
Note that FROM is replaced with a comma, and that what is extracted must be quoted. Additionally, extracting timezone-related information returns NULL.
last_day()
localtimestamp()As fractional seconds are not supported, this function doesn't take any arguments in this implementation, but empty parentheses are required: it must be called as LOCALTIMESTAMP().
months_between()
next_day()
numtoyminterval() This function returns in Oracle a datatype unknown of SQLtype, so in this implementation the function returns something looking like the Oracle result, mostly for demonstration purposes. Contrary to what can be done in Oracle, this result cannot be used in further calculations.
round(date)
sysdate()In this implementation SYSDATE() is a synonym of LOCALTIMESTAMP() and as the latter requires empty parentheses.
to_char(date)See comments under CONVERSION FUNCTIONS
to_yminterval()This function returns in Oracle a datatype unknown of SQLtype, so in this implementation the function returns something looking like the Oracle result, mostly for demonstration purposes. Contrary to what can be done in Oracle, this result cannot be used in further calculations.
trunc(date)

General comparison functions

Beware that there is no concept of collation in this library, and that characters are ordered by byte order.

FunctionComment
greatest()
least()

Conversion functions

FunctionComment
asciistr()There is a slight difference between this implementation of ASCIISTR() and the Oracle original: Oracle returns non-ASCII characters as UTF-16 code units. As UTF-8 is far more used than UTF-16 and is the default encoding for SQLite, this implementations returns non-ASCII characters as a sequence of two to four UTF-8 characters encoded as \xx, where xx is the hexadecimal representation of the byte. Thus
SELECT ASCIISTR('ABÄCDE')
returns
AB\c3\84CDE
The inverse function is UNISTR().
hextoraw()
rawtohex()
rowidtochar()The rowid in SQLite is simply a sequential row number within a table, whereas in Oracle it uniquely (if we put clusters aside) identifies a row within a database. This function simply mimics the Oracle function by returning its integer argument as an 18-character string; the encoding is plain hexadecimal encoding, which isn't the case in Oracle.
to_char(date) The third optional parameter in Oracle (nlsparam) isn't supported in this implementation. Language-dependent settings are derived from the current value of the LANG environment variable. A few minor differences with the Oracle implementation:
  • Spelling out of numbers is necessarily in English, independently of the settings of the LANG environment variable. Spelling may also slightly differ from what Oracle returns (eg TWO THOUSAND AND TWELVE versus TWENTY TWELVE)
  • The D format returns a value of 1-7 that indicates the number of the day in the week. Unfortunately, which day is 1 depends on database settings (territory). In this implementation, the ISO standard has been adopted and 1 is Monday in all cases with the D format.
  • Fractional seconds and timezones aren't supported. Related formats are accepted but ignored.
  • Era formats (EE and E) are also accepted but ignored.
  • Short and long formats are supported (DL, DS, TS) but DL always follows the American pattern irrespective of the language and DS defaults to YYYY-MM-DD and TS defaults to a 24-hour clock if LANG is not set.
to_char(number) The third optional parameter in Oracle (nlsparam) isn't supported in this implementation. Language-dependent settings are derived from the current value of the LANG environment variable. If a separator is required between thousands and none is defined by the locale, a UTF-8 thin space is used.
to_date() The third optional parameter in Oracle (nlsparam) isn't supported in this implementation. Language-dependent settings are derived from the current value of the LANG environment variable. A few minor differences with the Oracle implementation:
  • Era formats (EE and E) aren't supported.
  • Short and long formats are supported (DL, DS, TS) but DL always follows the American pattern irrespective of the language and DS defaults to YYYY-MM-DD and TS defaults to a 24-hour clock if LANG is not set.
to_number() The third optional parameter in Oracle (nlsparam) isn't supported in this implementation. Language-dependent settings are derived from the current value of the LANG environment variable.
unistr()UNISTR() assumes that its input may contain non-ASCII characters encoded as a sequence of backslash characters followed by a two-character hexadecimal value (see the description of ASCIISTR()) that it converts back to UTF-8 characters. As in Oracle, it's the inverse function of ASCIISTR().

Encoding decoding functions

FunctionComment
decode()
dump()Type numbers that are returned are the Oracle ones, but what is dumped is what SQLite actually stores.
ora_hash()
vsize()The function returns the actual size in SQLite, not the storage that the same data would use if stored in Oracle.

Null related functions

FunctionComment
lnnvl()
nvl()
nvl2()

Environment and Identifier functions

FunctionComment
sys_context() Namespace SYS_SESSION_ROLES is supported but always returns FALSE.
For namespace USERENV:
  • Information where applicable is pulled from the operating system. CURRENT and SESSION parameters return the same value.
  • Language and NLS_ parameters are all derived from the LANG environment variable.
  • BG_JOB_ID returns the current process id if the parent process id is 1, NULL otherwise. It's the opposite with FG_JOB_ID.
  • When no meaningful equivalent could be found, the function returns NULL.
user()Contrary to Oracle, USER() must be followed by empty parentheses.
userenv()

Aggregate functions

FunctionComment
corr()
covar_pop()
covar_samp()
listagg() Due to syntax constraints, there are significant differences between this implementation of LISTAGG and the original Oracle version. In Oracle LISTAGG is followed by a WITHIN GROUP clause that says how to order. In this implementation, this clause has been moved to the list of parameters, and LISTAGG must be used as follows:
LISTAGG(aggregated_column, sep, ordering_clause, ordering_col1, ordering_col2, ...)
All parameters except the first one are optional. The separator defaults to a comma, and the ordering clause is a character string composed of As and Ds; each character corresponds to one column following the clause (first character, first column, second character, second column, and so forth). A means ascending, D means descending. There may be fewer As and Ds than columns, ascending is the default.
regr_(linear regression) functions REGR_AVGX(), REGR_AVGY(), REGR_COUNT(), REGR_INTERCEPT() and REGR_SLOPE() are implemented.
stddev()
stddev_pop()
stddev_samp()
var_pop()
var_samp()
variance()

PostgreSQL functions for SQLite show Show details

Mathematical Functions

FunctionComment
abs()SQlite built-in function
cbrt()
ceil()
ceiling()Synonym for ceil()
div()
exp()
floor()
ln()
log()
mod()
pi()
power()
round()More sophisticated than the SQlite built-in function (can also round to powers of 10)
sign()
sqrt()
trunc()

Trigonometric Functions

FunctionComment
acos()
asin()
atan()
atan2()
cos()
cot()
sin()
tan()

String Functions

FunctionComment
bit_length()
char_length()
character_length()Synonym for char_length()
lower()Although SQLite provides this function, it doesn't work properly (as of SQLite 3.8) with accented characters. This implementation tries harder; it also works with Greek and Cyrillic characters.
octet_length()
overlay() The syntax isn't quite the same as in the original PostgreSQL version. The PostgreSQL syntax is
SELECT OVERLAY(source_string PLACING overlay_string FROM pos [ FOR len]), ...
this implementation requires
SELECT OVERLAY(source_string, overlay_string, pos[, len]), ...
Note that PLACING, FROM and FOR are replaced by commas.
position()Slight syntax difference with the PostgreSQL function. In PostgreSQL you write
SELECT POSITION(substring IN source_string), ...
this implementation requires
SELECT POSITION(substring, source_string), ...
IN is replaced with a comma.
substring()Syntax is slightly modified. Instead of
SELECT SUBSTRING(source_string FROM pos [ FOR len]), ...
this implementation requires
SELECT SUBSTRING(source_string, pos [ , len]), ...
The use of patterns (regular expressions) is also unsupported.
trim()The PostgreSQL syntax (standard SQL) is TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str). This implementation accepts between 1 and 3 parameters:
  • If there is a single argument, spaces are removed at both ends from the argument.
  • If there are two arguments, the first one is understood as what should be removed at both ends from the second one.
  • If there are three arguments, the first one must be one of BOTH, LEADING, or TRAILING (in upper or lower case, but it must be quoted), the second one is understood as what should be removed at the end(s) specified by the first argument, and the third argument is the trimmed string.
Note that the non-standard PostgreSQL version of trim(), namely trim([leading | trailing | both] [from] string [, characters] ) cannot be supported in this implementation because it would mean inverting the 2nd and 3rd parameter.
unaccent()Bonus function that doesn't exist in PostgreSQL (nor in MySQL or SQL Server or Oracle); it replaces every accented letter in the argument by its unaccented version. Note that:
  • No attempt is made to find a correct equivalent: ü is replaced with u, not with ue as, say, a German speaker might expect it.
  • The final result is not necessarily an ASCII string. The function also strips Greek characters, for instance, of their accents.
upper()Although SQLite provides this function, it doesn't work properly (as of SQLite 3.8) with accented characters. This implementation tries harder; it also works with Greek and Cyrillic characters.

Other String Functions

FunctionComment
ascii()
btrim()Equivalent to the SQLite built-in trim() function (which is overriden in the library)
chr()
concat()
concat_ws()
initcap()
left()left is an SQL keyword (left join). Renamed strleft() in this implementation.
length()SQlite built-in function
lpad()
ltrim()
md5()
quote_literal()Doesn't exactly behaves as the PostgreSQL version as a \ (backslash) is considered as a regular character by SQLite, not an escape character. This implementation, though, serves the same purpose as the PostgreSQL function, which is making data extracted from the database usable in a statement by escaping (doubling) quotes.
quote_nullable()Same remarks as for quote_literal().
repeat()
replace()SQlite built-in function
reverse()
right()right is an SQL keyword (right join). Renamed strright() in this implementation.
rpad()
rtrim()
split_part()
strpos()
substr()SQlite built-in function
to_hex()
translate()

Data Type Formatting Functions

FunctionComment
to_char()Spelling out (SP suffix) is partially implemented for dates (English only)
to_date()
to_number()
to_timestamp()No timezone when applied to a Unix timestamp

Date/Time Functions

PostgreSQL dates range from 4713 BC to 294276; in this implementation we have limited to the same range as Oracle, which means that the maximum possible date is 9999-12-31. PostgreSQL uses a proleptic Gregorian calendar, which in plain English means that it retroactively applies to past dates calendar reforms that took place far later (a commonly agreed practice). This is different from Oracle, that assumes that the whole world switched to the Gregorian calendar in October 1582 (which is wrong, even in Europe) and was using the Julian calendar before that (which was definitely wrong before 45BC). The difference between Julian and Gregorian calendar is in some leap years, plus some days that never were. Generally speaking, all DBMS products agree on dates after 1582. There may be disagreements about very old dates.

For these functions, the default date format is YYYY-MM-DD [HH24:MI:SS]. There is no attempt to "guess" the format. Timezones and fractional seconds aren't supported.

Note that intervals are automatically interpreted. You should NOT prefix the string representing the interval with the interval keyword, which is unknown of SQLite. Instead
SELECT JUSTIFY_DAYS('35 days')
will work as intended.See comments for justfy_days()
FunctionComment
age()
current_dateSQlite built-in function
current_timeSQlite built-in function
current_timestampSQlite built-in function
date()This is a function for implementing the special keywords recognized as dates by PostgreSQL, such as epoch or now. Thus
SELECT DATE('epoch')
will return
1970-01-01
date_part()
date_trunc()Seconds and sub-second date parts are supported but do nothing. No timezone in the timestamp that is returned.
extract() The syntax isn't quite the same as in the original PostgreSQL version. Where PostgreSQL wants
SELECT EXTRACT(YEAR FROM date_column), ...
this implementation requires
SELECT EXTRACT('YEAR', date_column), ...
Note that FROM is replaced with a comma, and that what is extracted must be quoted. Additionally, extracting timezone-related and sub-second information returns NULL.
isfinite()Returns 1 for true and 0 for false
justify_days()
justify_hours()
justify_interval()See comments for justfy_days()
localtime()Contrary to PostgreSQL, empty parentheses are required when calling the function
localtimestamp()Contrary to PostgreSQL, empty parentheses are required when calling the function. There are no fractional seconds, and the time is the current one when the function is called, not at the beginning of the transaction as in PostgreSQL.
now()Synonym for localtimestamp()
time()This is a function for implementing the special keywords recognized as times by PostgreSQL, such as epoch or now. Thus
SELECT TIME('allballs')
will return
00:00:00
timeofday()Returns the current timestamp in a text format (abbreviation for day of week and month). It doesn't include fractional seconds like PostgreSQL, but it's the only function in this library that returns the timezone name.
timestamp()This is a function for implementing the special keywords recognized as dates by PostgreSQL, such as epoch or now. Thus
SELECT TIMESTAMP('epoch')
will return
1970-01-01 00:00:00

Conditional Expressions

FunctionComment
greatest()
least()

General Purpose Aggregate Functions

FunctionComment
avg()SQlite built-in function
bit_and()
bit_or()
bool_and()Understands 0, 1, T, F, true, false (any case). Returns 0 or 1.
bool_or()Understands 0, 1, T, F, true, false (any case). Returns 0 or 1.
count()SQlite built-in function
every()Synonym for BOOL_AND()
json_agg()
json_object_agg()
max()SQlite built-in function
min()SQlite built-in function
string_agg()
sum()SQlite built-in function

Aggregate Functions for Statistics

FunctionComment
corr()
covar_pop()
covar_samp()
regr_avgx()
regr_avgy()
regr_count()
regr_intercept()
regr_slope()
stddev()
stddev_pop()
stddev_samp()
variance()
var_pop()
var_samp()

SQL Server functions for SQLite