0.27.1

This release adds the time extension to the single-file sqlean bundle.

0.27.0

This release introduces the new time extension — a structured, high-precision date/time API with a rich set of functions from working with Unix time to time comparison and arithmetic to truncation and rounding.

Creating time values:

time_now()
time_date(year, month, day[, hour, min, sec[, nsec[, offset_sec]]])

Extracting time fields:

time_get_year(t)
time_get_month(t)
time_get_day(t)
time_get_hour(t)
time_get_minute(t)
time_get_second(t)
time_get_nano(t)
time_get_weekday(t)
time_get_yearday(t)
time_get_isoyear(t)
time_get_isoweek(t)
time_get(t, field)

Unix time:

time_unix(sec[, nsec])
time_milli(msec)
time_micro(usec)
time_nano(nsec)
time_to_unix(t)
time_to_milli(t)
time_to_micro(t)
time_to_nano(t)

Time comparison:

time_after(t, u)
time_before(t, u)
time_compare(t, u)
time_equal(t, u)

Time arithmetic:

time_add(t, d)
time_add_date(t, years[, months[, days]])
time_sub(t, u)
time_since(t)
time_until(t)

Rounding:

time_trunc(t, field)
time_trunc(t, d)
time_round(t, d)

Formatting:

time_fmt_iso(t[, offset_sec])
time_fmt_datetime(t[, offset_sec])
time_fmt_date(t[, offset_sec])
time_fmt_time(t[, offset_sec])
time_parse(s)

Duration constants:

dur_ns()
dur_us()
dur_ms()
dur_s()
dur_m()
dur_h()

See the docs for full details.

0.26.0

This release introduces consistent naming. All functions are now prefixed with the module name:

• crypto_md5,
• fuzzy_hamming,
• stats_median,
• etc.

⚠️ The old names are retained for now for backward compatibility, but may be removed in future releases.

0.25.0

Unicode-aware case functions in the text extension. Powered by stc from @tylov. Thank you, Tyge!

⚠️ The unicode extension is now deprecated and will be removed in future releases. Use the text extension instead.

text_upper

Transforms a string to upper case.

select text_upper('cómo estás');
-- CÓMO ESTÁS

text_lower

Transforms a string to lower case.

select text_lower('CÓMO ESTÁS');
-- cómo estás

text_title

Transforms a string to title case.

select text_title('cómo estás');
-- Cómo Estás

text_like

Reports whether a string matches a pattern using the LIKE syntax.

select text_like('cóm_ está_', 'CÓMO ESTÁS');
-- 1

select text_like('ça%', 'Ça roule');
-- 1

text_nocase

The text_nocase collating sequence compares strings without regard to case.

select 1 where 'cómo estás' = 'CÓMO ESTÁS';
-- (null)

select 1 where 'cómo estás' = 'CÓMO ESTÁS' collate text_nocase;
-- 1

0.24.2

Escape quotes in define's undefine to prevent possible SQL injection, courtesy of @sivukhin. Thank you, Nikita!

0.24.1

UUIDv7 functions can now be disabled with the SQLEAN_OMIT_UUID7 compile-time flag (for older OS that do not support timespec_get). See #124 for details.

0.24.0

Functions for working with UUID v7 in the uuid extension, courtesy of @nghduc97. Thank you, Đức!

uuid7

Generate a version 7 (time-ordered, random) UUID.

select uuid7();
-- 018ff383-3e37-7615-b764-c241f544e573
select uuid7();
-- 018ff383-94fd-70fa-8da6-339180b8e15d

uuid7_timestamp_ms

Extract unix timestamp in miliseconds from version 7 UUID X. Returns null if the detected UUID version is not 7.

select uuid7_timestamp_ms('018ff38a-a5c9-712d-bc80-0550b3ad41a2');
-- 1717777901001
select datetime(uuid7_timestamp_ms('018ff38a-a5c9-712d-bc80-0550b3ad41a2') / 1000, 'unixepoch');
-- 2024-06-07 16:31:41
select uuid7_timestamp_ms(uuid4()) is null;
-- 1

0.23.0

Windows 32-bit build (sqlean-win-x86.zip), courtesy of @lucydodo. Thank you, SeongTae!

0.22.0

Linux ARM build (sqlean-linux-arm64.zip), courtesy of @flaviomartins. Thank you, Flavio!

0.21.10

Print the actual error message when regexp compilation fails (#104).

Before:

select regexp_substr('abc5xyz', 'a(?<=\D*)\d');
-- Runtime error: out of memory (7)

Now:

select regexp_substr('abc5xyz', 'a(?<=\D*)\d');
-- Runtime error: lookbehind assertion is not fixed length (offset 1)