Space and Time supports standard SQL as found in any RDBMS (and for which tutorials abound), however, there some "special functions" available in Space and Time that are particularly useful. This is a subset of the overall capability - the more 'standard' SQL functions are not included here.

UUID()

Returns a randomly generated UUID, or universally unique identifier, and is useful when a unique value is required (say, for a surrogate primary key).

SELECT UUID() FROM SXTLabs.Singularity;

SELECT UUID(), count(*) FROM ETHEREUM.BLOCKS;

Returns a randomly generated UUID, such as: 65e56470-6511-4b92-a092-a090c93dc6e0

from_json()

Merges a json document from a string, and the structure metadata definition from a string, and produces a json document that can be queried using dot-notation.

SELECT from_json( Nearest_Star_json, 
'STRUCT<name:STRING, planets:INTEGER, age:DECIMAL, size: STRUCT<diameter:DECIMAL, density:STRING, gravity: STRING>, planet_names:ARRAY<STRING>>') AS myjson
,myjson.name
,myjson.planets
,myjson.age
,myjson.size.diameter
,myjson.size.density
,myjson.size.gravity
,myjson.planet_names
FROM SXTLABS.Singularity

Returns columns employee_name, age, department and department, extracting the row values from the json_column.

rank()

Computes the rank of a value among rows in a table. Rows will create gaps in the rank.

SELECT employee_name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM pmtest.EMPLOYEES;

Returns employee_name, salary and rank based on salary.

employee_name salary rank
andrew jackson	140000	1
sam adams	120000	2
john jefferson	120000	2
charles madison	110000	4
john smith	100000	5

dense_rank()

Similar to rank, computes the rank of a value among rows in a table, however, unlike rank, dense_rank will not produce gaps in the ranking sequence.

SELECT employee_name, salary,DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM pmtest.EMPLOYEES;

Returns employee_name, salary and rank based on salary.

employee_name salary rank
andrew jackson	140000	1
sam adams	120000	2
john jefferson	120000	2
charles madison	110000	3
john smith	100000	4

Date and Time Functions

current_date

Returns the current date at the start of query evaluation.
Note, unless otherwise specified, this is UTC, aka timezone +00:00

SELECT current_date FROM SXTLabs.Singularity;

SELECT * FROM SXTLABS.SINGULARITY WHERE cast(LAST_UPDATED AS date) <> current_date

curdate()

Returns the current date at the start of query evaluation.

SELECT * FROM SXTDEMO.STOCKS WHERE CAST(STOCK_DATE AS DATE) = CURDATE();

Returns all the rows with TIME_STAMP equal to the current date.

date_add()

Adds the integer number of days to a date or timestamp.

WITH dt AS (SELECT current_date AS today FROM SXTLABS.SINGULARITY)
SELECT today
,date_add(today,  1) AS tomorrow
,date_add(today, -1) AS yeseterday
FROM dt

Returns one row with three dates; today's date, tomorrow's date (+1) and yesterday's date (-1).

dateadd()

SELECT * FROM SXTDEMO.STOCKS WHERE STOCK_DATE > DATEADD(month, -4, CURDATE());

Returns all the rows since three days before the current date;

date_format()

Converts timestamp to a value of string in the format specified by the date format

SELECT SYMBOL, STOCK_CLOSE, date_format(STOCK_DATE, 'mm-dd') FROM SXTDEMO.STOCKS ORDER BY STOCK_DATE DESC LIMIT 4;

Returns the date in the format specified

GOOGL	1.3259E-14	00-10
MSFT	3.6967E-14	00-10
AMZN	1.4356E-14	00-10
AAPL	1.864E-15	00-10

date_diff()

Returns the number of days from start_date to end_date.

SELECT
    CURRENT_DATE, 
    STOCK_DATE, 
    STOCK_CLOSE,
    DATE_DIFF(CURRENT_DATE, STOCK_DATE)
FROM 
    SXTDEMO.STOCKS
WHERE 
    SYMBOL = 'GOOGL' AND 
    STOCK_CLOSE < 132 AND
    STOCK_DATE = (SELECT MAX(STOCK_DATE) FROM SXTDEMO.STOCKS WHERE SYMBOL = 'GOOGL' AND STOCK_CLOSE < 132)
ORDER BY 
    STOCK_DATE DESC;

Returns the number of days from the current_date since stock_date.

GOOGL	2023-11-14	2023-11-10	1.3259E-14	4

TimestampDiff()

Accepts a time length and two timestamps, and delivers the difference in the requested time length.

select 
  LAST_UPDATED
, current_timestamp
, TimestampDiff(Second, LAST_UPDATED, current_timestamp) as Lag_Seconds
, TimestampDiff(Minute, LAST_UPDATED, current_timestamp) as Lag_Minutes
, TimestampDiff(Hour, LAST_UPDATED, current_timestamp) as Lag_Hours
, TimestampDiff(day, LAST_UPDATED, current_timestamp) as Lag_Days
from SXTLABS.SINGULARITY

Analytic Engine Functions

The big-data functions below will only work on Space and Time's analytic engine.

corr()

corr(expr1, expr2) - Returns Pearson coefficient of correlation between a set of number pairs.

SELECT corr(STOCK_VOLUME, STOCK_CLOSE) FROM SXTDEMO.STOCKS;

Returns the correlation between STOCK_VOLUME and STOCK_CLOSE, for example: -0.3106348913452836.

histogram_numeric()

histogram_numeric(expr, nb) - Computes a histogram on numeric 'expr' using nb bins.

SELECT histogram_numeric(STOCK_VOLUME, 5) FROM SXTDEMO.STOCKS;

Returns a histogram with the STOCK_VOLUME column with 5 bins, for example:
[{"x":35024959,"y":355.0},{"x":62998095,"y":181.0},{"x":88730741,"y":24.0},{"x":121063212,"y":8.0},{"x":151711875,"y":4.0}].