The need arose to convert some SQL to Nim, plain local dates with no indication of daylight saving. But there is no julian day in Nim?
UPDATE timestamp
SET ts =
strftime('%Y-%m-%dT%H:%M:%S', ts) ||
printf('%+03d:%02d',
CAST(ROUND((julianday(ts || 'Z', 'localtime') - julianday(ts || 'Z')) * 24 * 60 / 15, 0) * 15 AS INTEGER) / 60,
ABS(CAST(ROUND((julianday(ts || 'Z', 'localtime') - julianday(ts || 'Z')) * 24 * 60 / 15, 0) * 15 AS INTEGER)) % 60
)
WHERE ts NOT LIKE '%+__:__'
AND ts NOT LIKE '%-__:__';
import std/[times, math, strformat]
import julian
proc toIsoWithOffset(tsStr: string): string =
let utcTime = parse(tsStr, "yyyy-MM-dd'T'HH:mm:ss", utc())
let jdUTC = julianDay(utcTime)
let jdUTCValue = jdUTC.julianDay.float + jdUTC.julianDayFraction
let localTime = parse(tsStr, "yyyy-MM-dd'T'HH:mm:ss", local())
let jdLocal = julianDay(localTime)
let jdLocalValue = jdLocal.julianDay.float + jdLocal.julianDayFraction
let diffDays = jdLocalValue - jdUTCValue
let diffMinutes = diffDays * 24 * 60
let roundedMinutes = round(diffMinutes / 15).toInt * 15
let offsetMinutes = -roundedMinutes
let sign = if offsetMinutes >= 0: "+" else: "-"
let absMin = abs(offsetMinutes)
let hours = absMin div 60
let minutes = absMin mod 60
result = fmt"{tsStr}{sign}{hours:02d}:{minutes:02d}"
when isMainModule:
echo toIsoWithOffset("2024-03-01T12:00:00") # +01:00
echo toIsoWithOffset("2024-07-01T12:00:00") # +02:00So I made a minimal julian thingy:
https://gist.github.com/ingoogni/0dc35a7f7552240bd1743d425aaa509c