///|
/// Proleptic Gregorian arithmetic, independent of host Date/timezone APIs.
fn date_floor(a : Int64, b : Int64) -> Int64 {
let q = a / b
if a % b < 0L {
q - 1L
} else {
q
}
}
///|
fn civil_days(year : Int64, month : Int64, day : Int64) -> Int64 {
let y = year - (if month <= 2L { 1L } else { 0L })
let era = date_floor(y, 400L)
let yoe = y - era * 400L
let m = month + (if month > 2L { -3L } else { 9L })
era * 146097L +
yoe * 365L +
yoe / 4L -
yoe / 100L +
(153L * m + 2L) / 5L +
day -
1L -
719468L
}
///|
fn date_civil(days : Int64) -> (Int64, Int64, Int64) {
let z = days + 719468L
let era = date_floor(z, 146097L)
let doe = z - era * 146097L
let yoe = (doe - doe / 1460L + doe / 36524L - doe / 146096L) / 365L
let y = yoe + era * 400L
let doy = doe - (365L * yoe + yoe / 4L - yoe / 100L)
let mp = (5L * doy + 2L) / 153L
let d = doy - (153L * mp + 2L) / 5L + 1L
let m = mp + (if mp < 10L { 3L } else { -9L })
(y + (if m <= 2L { 1L } else { 0L }), m, d)
}
///|
fn date_pad(n : Int64, width : Int32) -> String {
let s = n.abs().to_string()
(if n < 0L { "-" } else { "" }) + decimal_zeros(width - s.length()) + s
}
///|
fn date_digits(b : Bytes, start : Int32, count : Int32) -> Int64? {
if start < 0 || start + count > b.length() {
return None
}
let mut value = 0L
for i in start..<(start + count) {
if b[i] < b'0' || b[i] > b'9' {
return None
}
value = value * 10L + (b[i].to_int() - 48).to_int64()
}
Some(value)
}
///|
fn date_parse(
value : Value,
epoch : Bool,
auto : Bool,
clock : Int64?,
) -> Int64? {
if value is Null {
return None
}
if identifier(text_value(value)) == "now" {
return clock
}
let raw = text_value(value)
let number = match value {
Integer(n) => Some(n.to_double())
Real(n) => Some(n)
_ =>
match cast_numeric(value) {
Integer(_) | Real(_) =>
Some(@string.parse_double(raw)) catch {
_ => None
}
_ => None
}
}
if number is Some(n) {
let ms = if epoch || (auto && (n < 0.0 || n >= 5373484.5)) {
n * 1000.0
} else {
(n - 2440587.5) * 86400000.0
}
if !ms.is_finite() || ms < -210866760000000.0 || ms > 253402300799999.0 {
return None
}
return Some((ms + 210866760000000.5).to_int64() - 210866760000000L)
}
let b = @utf8.encode(raw)
let mut pos = 0
let mut y = 2000L
let mut m = 1L
let mut d = 1L
if b.length() >= 10 && b[4] == b'-' && b[7] == b'-' {
guard date_digits(b, 0, 4) is Some(yy) &&
date_digits(b, 5, 2) is Some(mm) &&
date_digits(b, 8, 2) is Some(dd) else {
return None
}
y = yy
m = mm
d = dd
pos = 10
if m < 1L || m > 12L || d < 1L || d > 31L {
return None
}
while pos < b.length() && (b[pos] == b' ' || b[pos] == b'T') {
pos += 1
}
}
let mut hour = 0L
let mut minute = 0L
let mut second = 0L
let mut fraction = 0L
if pos < b.length() && b[pos] != b'Z' && b[pos] != b'z' {
guard pos + 5 <= b.length() &&
b[pos + 2] == b':' &&
date_digits(b, pos, 2) is Some(h) &&
date_digits(b, pos + 3, 2) is Some(mi) else {
return None
}
hour = h
minute = mi
pos += 5
if pos < b.length() && b[pos] == b':' {
guard date_digits(b, pos + 1, 2) is Some(sec) else { return None }
second = sec
pos += 3
if pos < b.length() && b[pos] == b'.' {
pos += 1
let start = pos
while pos < b.length() && b[pos] >= b'0' && b[pos] <= b'9' {
pos += 1
}
if pos == start {
return None
}
let digits = @utf8.decode_lossy(b[start:pos]) + "000"
fraction = ((@string.parse_double("0." + digits) catch { _ => 0.0 }) *
1000.0 +
0.5).to_int64()
}
}
if hour > 24L || minute > 59L || second > 59L {
return None
}
}
let mut offset = 0L
while pos < b.length() && b[pos] == b' ' {
pos += 1
}
if pos < b.length() && (b[pos] == b'+' || b[pos] == b'-') {
let sign = if b[pos] == b'-' { -1L } else { 1L }
guard pos + 6 <= b.length() &&
b[pos + 3] == b':' &&
date_digits(b, pos + 1, 2) is Some(h) &&
date_digits(b, pos + 4, 2) is Some(mi) &&
h <= 14L &&
mi < 60L else {
return None
}
offset = sign * (h * 60L + mi) * 60000L
pos += 6
} else if pos < b.length() && (b[pos] == b'Z' || b[pos] == b'z') {
pos += 1
}
while pos < b.length() && b[pos] == b' ' {
pos += 1
}
if pos != b.length() || b.length() == 0 {
return None
}
Some(
civil_days(y, m, d) * 86400000L +
hour * 3600000L +
minute * 60000L +
second * 1000L +
fraction -
offset,
)
}
///|
fn datetime_arity(name : String, count : Int32) -> Bool {
["date", "time", "datetime", "julianday", "unixepoch"].contains(name) ||
(name == "strftime" && count >= 1)
}
///|
fn datetime_call(
name : String,
values : Array[Value],
clock : Int64?,
) -> Value raise SqlError {
for value in values {
if value is Blob(_) {
ignore(checked_text_value(value))
}
}
let begin = if name == "strftime" { 1 } else { 0 }
if values.any(v => v is Null) {
return Null
}
let input = if values.length() > begin { values[begin] } else { Text("now") }
let mods = if values.length() > begin + 1 {
values[begin + 1:].to_owned().map(v => identifier(text_value(v)))
} else {
[]
}
let epoch = !mods.is_empty() && mods[0] == "unixepoch"
let auto = !mods.is_empty() && mods[0] == "auto"
guard date_parse(input, epoch, auto, clock) is Some(parsed) else {
return Null
}
let mut ms = parsed
let mut overflow = 0L
let mut subsec = false
for i, modifier in mods {
if i == 0 &&
(modifier == "unixepoch" || modifier == "auto" || modifier == "julianday") {
continue
}
if modifier == "subsec" || modifier == "subsecond" {
subsec = true
continue
}
if modifier == "floor" {
ms -= overflow * 86400000L
overflow = 0L
continue
}
if modifier == "ceiling" {
overflow = 0L
continue
}
overflow = 0L
let days = date_floor(ms, 86400000L)
let remainder = ms - days * 86400000L
let (y, m, d) = date_civil(days)
if modifier == "start of day" {
ms = days * 86400000L
} else if modifier == "start of month" {
ms = civil_days(y, m, 1L) * 86400000L
} else if modifier == "start of year" {
ms = civil_days(y, 1L, 1L) * 86400000L
} else if modifier.has_prefix("weekday ") {
guard (@string.parse_int64(modifier[8:].to_owned()) catch { _ => -1L })
is n &&
n >= 0L &&
n <= 6L else {
return Null
}
let weekday = ((days + 4L) % 7L + 7L) % 7L
ms += (n - weekday + 7L) % 7L * 86400000L
} else {
let parts = modifier.split(" ").to_array().filter(v => !v.is_empty())
if parts.length() != 2 {
return Null
}
let n = @string.parse_double(parts[0].to_owned()) catch {
_ => return Null
}
let unit = parts[1].to_owned().trim_end(chars="s").to_owned()
if unit == "month" || unit == "year" {
let months = if unit == "year" { n * 12.0 } else { n }
let whole = months.to_int64()
let target = y * 12L + m - 1L + whole
let yy = date_floor(target, 12L)
let mm = target - yy * 12L + 1L
let max = civil_days(yy, mm + 1L, 1L) - civil_days(yy, mm, 1L)
ms = civil_days(yy, mm, d) * 86400000L +
remainder +
((months - whole.to_double()) * 30.0 * 86400000.0).to_int64()
overflow = (d - max).max(0L)
} else {
let factor = match unit {
"day" => 86400000.0
"hour" => 3600000.0
"minute" => 60000.0
"second" => 1000.0
_ => return Null
}
let increment = n * factor
if !increment.is_finite() || increment.abs() > 464269060799999.0 {
return Null
}
ms += increment.to_int64()
}
}
}
if ms < -210866760000000L || ms > 253402300799999L {
return Null
}
if name == "julianday" {
return Real(ms.to_double() / 86400000.0 + 2440587.5)
}
if name == "unixepoch" {
return if subsec {
Real(ms.to_double() / 1000.0)
} else {
Integer(date_floor(ms, 1000L))
}
}
let days = date_floor(ms, 86400000L)
let (y, m, d) = date_civil(days)
let rest = ms - days * 86400000L
let hour = rest / 3600000L
let minute = rest / 60000L % 60L
let second = rest / 1000L % 60L
let date = date_pad(y, 4) + "-" + date_pad(m, 2) + "-" + date_pad(d, 2)
let time = date_pad(hour, 2) +
":" +
date_pad(minute, 2) +
":" +
date_pad(second, 2) +
(if subsec { "." + date_pad(rest % 1000L, 3) } else { "" })
if name == "date" {
return Text(date)
}
if name == "time" {
return Text(time)
}
if name == "datetime" {
return Text(date + " " + time)
}
let format = @utf8.encode(text_value(values[0]))
let out = @buffer.Buffer()
let mut i = 0
while i < format.length() {
if format[i] != b'%' {
out.write_byte(format[i])
i += 1
continue
}
i += 1
if i == format.length() {
return Null
}
let value = match format[i].to_int() {
37 => "%"
89 => date_pad(y, 4)
109 => date_pad(m, 2)
100 => date_pad(d, 2)
101 => d.to_string()
72 => date_pad(hour, 2)
107 => hour.to_string()
73 => date_pad((hour + 11L) % 12L + 1L, 2)
108 => ((hour + 11L) % 12L + 1L).to_string()
77 => date_pad(minute, 2)
83 => date_pad(second, 2)
102 => date_pad(second, 2) + "." + date_pad(rest % 1000L, 3)
70 => date
82 => date_pad(hour, 2) + ":" + date_pad(minute, 2)
84 =>
date_pad(hour, 2) +
":" +
date_pad(minute, 2) +
":" +
date_pad(second, 2)
115 => date_floor(ms, 1000L).to_string()
119 => (((days + 4L) % 7L + 7L) % 7L).to_string()
117 => (((days + 3L) % 7L + 7L) % 7L + 1L).to_string()
106 => date_pad(days - civil_days(y, 1L, 1L) + 1L, 3)
85 =>
date_pad(
(days - civil_days(y, 1L, 1L) + 7L - ((days + 4L) % 7L + 7L) % 7L) /
7L,
2,
)
87 =>
date_pad(
(days - civil_days(y, 1L, 1L) + 7L - ((days + 3L) % 7L + 7L) % 7L) /
7L,
2,
)
86 | 71 | 103 => {
let thursday = days + 3L - ((days + 3L) % 7L + 7L) % 7L
let (iso_year, _, _) = date_civil(thursday)
if format[i].to_int() == 71 {
date_pad(iso_year, 4)
} else if format[i].to_int() == 103 {
date_pad(iso_year % 100L, 2)
} else {
date_pad((thursday - civil_days(iso_year, 1L, 1L)) / 7L + 1L, 2)
}
}
112 => if hour < 12L { "AM" } else { "PM" }
80 => if hour < 12L { "am" } else { "pm" }
74 => text_value(Real(ms.to_double() / 86400000.0 + 2440587.5))
_ => return Null
}
out.write_bytes(@utf8.encode(value))
i += 1
}
Text(@utf8.decode_lossy(out.to_bytes()))
}
///|
/// One clock instant is supplied by the host before executing a statement.
pub fn database_set_clock(db : Database, milliseconds : String) -> String {
try {
let value = @string.parse_int64(milliseconds)
db.store.triggers.clock = Some(value)
"{}"
} catch {
e => public_error_json(e).stringify()
}
}