1package sqlite3
2
3import (
4 "math"
5 "strconv"
6 "strings"
7 "time"
8
9 "github.com/ncruces/go-sqlite3/internal/util"
10 "github.com/ncruces/julianday"
11)
12
13// TimeFormat specifies how to encode/decode time values.
14//
15// See the documentation for the [TimeFormatDefault] constant
16// for formats recognized by SQLite.
17//
18// https://sqlite.org/lang_datefunc.html
19type TimeFormat string
20
21// TimeFormats recognized by SQLite to encode/decode time values.
22//
23// https://sqlite.org/lang_datefunc.html#time_values
24const (
25 TimeFormatDefault TimeFormat = "" // time.RFC3339Nano
26
27 // Text formats
28 TimeFormat1 TimeFormat = "2006-01-02"
29 TimeFormat2 TimeFormat = "2006-01-02 15:04"
30 TimeFormat3 TimeFormat = "2006-01-02 15:04:05"
31 TimeFormat4 TimeFormat = "2006-01-02 15:04:05.000"
32 TimeFormat5 TimeFormat = "2006-01-02T15:04"
33 TimeFormat6 TimeFormat = "2006-01-02T15:04:05"
34 TimeFormat7 TimeFormat = "2006-01-02T15:04:05.000"
35 TimeFormat8 TimeFormat = "15:04"
36 TimeFormat9 TimeFormat = "15:04:05"
37 TimeFormat10 TimeFormat = "15:04:05.000"
38
39 TimeFormat2TZ = TimeFormat2 + "Z07:00"
40 TimeFormat3TZ = TimeFormat3 + "Z07:00"
41 TimeFormat4TZ = TimeFormat4 + "Z07:00"
42 TimeFormat5TZ = TimeFormat5 + "Z07:00"
43 TimeFormat6TZ = TimeFormat6 + "Z07:00"
44 TimeFormat7TZ = TimeFormat7 + "Z07:00"
45 TimeFormat8TZ = TimeFormat8 + "Z07:00"
46 TimeFormat9TZ = TimeFormat9 + "Z07:00"
47 TimeFormat10TZ = TimeFormat10 + "Z07:00"
48
49 // Numeric formats
50 TimeFormatJulianDay TimeFormat = "julianday"
51 TimeFormatUnix TimeFormat = "unixepoch"
52 TimeFormatUnixFrac TimeFormat = "unixepoch_frac"
53 TimeFormatUnixMilli TimeFormat = "unixepoch_milli" // not an SQLite format
54 TimeFormatUnixMicro TimeFormat = "unixepoch_micro" // not an SQLite format
55 TimeFormatUnixNano TimeFormat = "unixepoch_nano" // not an SQLite format
56
57 // Auto
58 TimeFormatAuto TimeFormat = "auto"
59)
60
61// Encode encodes a time value using this format.
62//
63// [TimeFormatDefault] and [TimeFormatAuto] encode using [time.RFC3339Nano],
64// with nanosecond accuracy, and preserving any timezone offset.
65//
66// This is the format used by the [database/sql] driver:
67// [database/sql.Row.Scan] will decode as [time.Time]
68// values encoded with [time.RFC3339Nano].
69//
70// Time values encoded with [time.RFC3339Nano] cannot be sorted as strings
71// to produce a time-ordered sequence.
72//
73// Assuming that the time zones of the time values are the same (e.g., all in UTC),
74// and expressed using the same string (e.g., all "Z" or all "+00:00"),
75// use the TIME [collating sequence] to produce a time-ordered sequence.
76//
77// Otherwise, use [TimeFormat7] for time-ordered encoding.
78//
79// Formats [TimeFormat1] through [TimeFormat10]
80// convert time values to UTC before encoding.
81//
82// Returns a string for the text formats,
83// a float64 for [TimeFormatJulianDay] and [TimeFormatUnixFrac],
84// or an int64 for the other numeric formats.
85//
86// https://sqlite.org/lang_datefunc.html
87//
88// [collating sequence]: https://sqlite.org/datatype3.html#collating_sequences
89func (f TimeFormat) Encode(t time.Time) any {
90 switch f {
91 // Numeric formats
92 case TimeFormatJulianDay:
93 return julianday.Float(t)
94 case TimeFormatUnix:
95 return t.Unix()
96 case TimeFormatUnixFrac:
97 return float64(t.Unix()) + float64(t.Nanosecond())*1e-9
98 case TimeFormatUnixMilli:
99 return t.UnixMilli()
100 case TimeFormatUnixMicro:
101 return t.UnixMicro()
102 case TimeFormatUnixNano:
103 return t.UnixNano()
104 // Special formats.
105 case TimeFormatDefault, TimeFormatAuto:
106 f = time.RFC3339Nano
107 // SQLite assumes UTC if unspecified.
108 case
109 TimeFormat1, TimeFormat2,
110 TimeFormat3, TimeFormat4,
111 TimeFormat5, TimeFormat6,
112 TimeFormat7, TimeFormat8,
113 TimeFormat9, TimeFormat10:
114 t = t.UTC()
115 }
116 return t.Format(string(f))
117}
118
119// Decode decodes a time value using this format.
120//
121// The time value can be a string, an int64, or a float64.
122//
123// Formats [TimeFormat8] through [TimeFormat10]
124// (and [TimeFormat8TZ] through [TimeFormat10TZ])
125// assume a date of 2000-01-01.
126//
127// The timezone indicator and fractional seconds are always optional
128// for formats [TimeFormat2] through [TimeFormat10]
129// (and [TimeFormat2TZ] through [TimeFormat10TZ]).
130//
131// [TimeFormatAuto] implements (and extends) the SQLite auto modifier.
132// Julian day numbers are safe to use for historical dates,
133// from 4712BC through 9999AD.
134// Unix timestamps (expressed in seconds, milliseconds, microseconds, or nanoseconds)
135// are safe to use for current events, from at least 1980 through at least 2260.
136// Unix timestamps before 1980 and after 9999 may be misinterpreted as julian day numbers,
137// or have the wrong time unit.
138//
139// https://sqlite.org/lang_datefunc.html
140func (f TimeFormat) Decode(v any) (time.Time, error) {
141 if t, ok := v.(time.Time); ok {
142 return t, nil
143 }
144 switch f {
145 // Numeric formats.
146 case TimeFormatJulianDay:
147 switch v := v.(type) {
148 case string:
149 return julianday.Parse(v)
150 case float64:
151 return julianday.FloatTime(v), nil
152 case int64:
153 return julianday.Time(v, 0), nil
154 default:
155 return time.Time{}, util.TimeErr
156 }
157
158 case TimeFormatUnix, TimeFormatUnixFrac:
159 if s, ok := v.(string); ok {
160 f, err := strconv.ParseFloat(s, 64)
161 if err != nil {
162 return time.Time{}, err
163 }
164 v = f
165 }
166 switch v := v.(type) {
167 case float64:
168 sec, frac := math.Modf(v)
169 nsec := math.Floor(frac * 1e9)
170 return time.Unix(int64(sec), int64(nsec)).UTC(), nil
171 case int64:
172 return time.Unix(v, 0).UTC(), nil
173 default:
174 return time.Time{}, util.TimeErr
175 }
176
177 case TimeFormatUnixMilli:
178 if s, ok := v.(string); ok {
179 i, err := strconv.ParseInt(s, 10, 64)
180 if err != nil {
181 return time.Time{}, err
182 }
183 v = i
184 }
185 switch v := v.(type) {
186 case float64:
187 return time.UnixMilli(int64(math.Floor(v))).UTC(), nil
188 case int64:
189 return time.UnixMilli(v).UTC(), nil
190 default:
191 return time.Time{}, util.TimeErr
192 }
193
194 case TimeFormatUnixMicro:
195 if s, ok := v.(string); ok {
196 i, err := strconv.ParseInt(s, 10, 64)
197 if err != nil {
198 return time.Time{}, err
199 }
200 v = i
201 }
202 switch v := v.(type) {
203 case float64:
204 return time.UnixMicro(int64(math.Floor(v))).UTC(), nil
205 case int64:
206 return time.UnixMicro(v).UTC(), nil
207 default:
208 return time.Time{}, util.TimeErr
209 }
210
211 case TimeFormatUnixNano:
212 if s, ok := v.(string); ok {
213 i, err := strconv.ParseInt(s, 10, 64)
214 if err != nil {
215 return time.Time{}, util.TimeErr
216 }
217 v = i
218 }
219 switch v := v.(type) {
220 case float64:
221 return time.Unix(0, int64(math.Floor(v))).UTC(), nil
222 case int64:
223 return time.Unix(0, v).UTC(), nil
224 default:
225 return time.Time{}, util.TimeErr
226 }
227
228 // Special formats.
229 case TimeFormatAuto:
230 switch s := v.(type) {
231 case string:
232 i, err := strconv.ParseInt(s, 10, 64)
233 if err == nil {
234 v = i
235 break
236 }
237 f, err := strconv.ParseFloat(s, 64)
238 if err == nil {
239 v = f
240 break
241 }
242
243 dates := []TimeFormat{
244 TimeFormat9, TimeFormat8,
245 TimeFormat6, TimeFormat5,
246 TimeFormat3, TimeFormat2, TimeFormat1,
247 }
248 for _, f := range dates {
249 t, err := f.Decode(s)
250 if err == nil {
251 return t, nil
252 }
253 }
254 }
255 switch v := v.(type) {
256 case float64:
257 if 0 <= v && v < 5373484.5 {
258 return TimeFormatJulianDay.Decode(v)
259 }
260 if v < 253402300800 {
261 return TimeFormatUnixFrac.Decode(v)
262 }
263 if v < 253402300800_000 {
264 return TimeFormatUnixMilli.Decode(v)
265 }
266 if v < 253402300800_000000 {
267 return TimeFormatUnixMicro.Decode(v)
268 }
269 return TimeFormatUnixNano.Decode(v)
270 case int64:
271 if 0 <= v && v < 5373485 {
272 return TimeFormatJulianDay.Decode(v)
273 }
274 if v < 253402300800 {
275 return TimeFormatUnixFrac.Decode(v)
276 }
277 if v < 253402300800_000 {
278 return TimeFormatUnixMilli.Decode(v)
279 }
280 if v < 253402300800_000000 {
281 return TimeFormatUnixMicro.Decode(v)
282 }
283 return TimeFormatUnixNano.Decode(v)
284 default:
285 return time.Time{}, util.TimeErr
286 }
287
288 case
289 TimeFormat2, TimeFormat2TZ,
290 TimeFormat3, TimeFormat3TZ,
291 TimeFormat4, TimeFormat4TZ,
292 TimeFormat5, TimeFormat5TZ,
293 TimeFormat6, TimeFormat6TZ,
294 TimeFormat7, TimeFormat7TZ:
295 s, ok := v.(string)
296 if !ok {
297 return time.Time{}, util.TimeErr
298 }
299 return f.parseRelaxed(s)
300
301 case
302 TimeFormat8, TimeFormat8TZ,
303 TimeFormat9, TimeFormat9TZ,
304 TimeFormat10, TimeFormat10TZ:
305 s, ok := v.(string)
306 if !ok {
307 return time.Time{}, util.TimeErr
308 }
309 t, err := f.parseRelaxed(s)
310 if err != nil {
311 return time.Time{}, err
312 }
313 return t.AddDate(2000, 0, 0), nil
314
315 default:
316 s, ok := v.(string)
317 if !ok {
318 return time.Time{}, util.TimeErr
319 }
320 if f == "" {
321 f = time.RFC3339Nano
322 }
323 return time.Parse(string(f), s)
324 }
325}
326
327func (f TimeFormat) parseRelaxed(s string) (time.Time, error) {
328 fs := string(f)
329 fs = strings.TrimSuffix(fs, "Z07:00")
330 fs = strings.TrimSuffix(fs, ".000")
331 t, err := time.Parse(fs+"Z07:00", s)
332 if err != nil {
333 return time.Parse(fs, s)
334 }
335 return t, nil
336}
337
338// Scanner returns a [database/sql.Scanner] that can be used as an argument to
339// [database/sql.Row.Scan] and similar methods to
340// decode a time value into dest using this format.
341func (f TimeFormat) Scanner(dest *time.Time) interface{ Scan(any) error } {
342 return timeScanner{dest, f}
343}
344
345type timeScanner struct {
346 *time.Time
347 TimeFormat
348}
349
350func (s timeScanner) Scan(src any) error {
351 var ok bool
352 var err error
353 if *s.Time, ok = src.(time.Time); !ok {
354 *s.Time, err = s.Decode(src)
355 }
356 return err
357}