github.com/ncruces/go-sqlite3@v0.15.1-0.20240520133447-53eef1510ff0/time.go (about)

     1  package sqlite3
     2  
     3  import (
     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
    19  type TimeFormat string
    20  
    21  // TimeFormats recognized by SQLite to encode/decode time values.
    22  //
    23  // https://sqlite.org/lang_datefunc.html#time_values
    24  const (
    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
    89  func (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
   140  func (f TimeFormat) Decode(v any) (time.Time, error) {
   141  	switch f {
   142  	// Numeric formats
   143  	case TimeFormatJulianDay:
   144  		switch v := v.(type) {
   145  		case string:
   146  			return julianday.Parse(v)
   147  		case float64:
   148  			return julianday.FloatTime(v), nil
   149  		case int64:
   150  			return julianday.Time(v, 0), nil
   151  		default:
   152  			return time.Time{}, util.TimeErr
   153  		}
   154  
   155  	case TimeFormatUnix, TimeFormatUnixFrac:
   156  		if s, ok := v.(string); ok {
   157  			f, err := strconv.ParseFloat(s, 64)
   158  			if err != nil {
   159  				return time.Time{}, err
   160  			}
   161  			v = f
   162  		}
   163  		switch v := v.(type) {
   164  		case float64:
   165  			sec, frac := math.Modf(v)
   166  			nsec := math.Floor(frac * 1e9)
   167  			return time.Unix(int64(sec), int64(nsec)).UTC(), nil
   168  		case int64:
   169  			return time.Unix(v, 0).UTC(), nil
   170  		default:
   171  			return time.Time{}, util.TimeErr
   172  		}
   173  
   174  	case TimeFormatUnixMilli:
   175  		if s, ok := v.(string); ok {
   176  			i, err := strconv.ParseInt(s, 10, 64)
   177  			if err != nil {
   178  				return time.Time{}, err
   179  			}
   180  			v = i
   181  		}
   182  		switch v := v.(type) {
   183  		case float64:
   184  			return time.UnixMilli(int64(math.Floor(v))).UTC(), nil
   185  		case int64:
   186  			return time.UnixMilli(int64(v)).UTC(), nil
   187  		default:
   188  			return time.Time{}, util.TimeErr
   189  		}
   190  
   191  	case TimeFormatUnixMicro:
   192  		if s, ok := v.(string); ok {
   193  			i, err := strconv.ParseInt(s, 10, 64)
   194  			if err != nil {
   195  				return time.Time{}, err
   196  			}
   197  			v = i
   198  		}
   199  		switch v := v.(type) {
   200  		case float64:
   201  			return time.UnixMicro(int64(math.Floor(v))).UTC(), nil
   202  		case int64:
   203  			return time.UnixMicro(int64(v)).UTC(), nil
   204  		default:
   205  			return time.Time{}, util.TimeErr
   206  		}
   207  
   208  	case TimeFormatUnixNano:
   209  		if s, ok := v.(string); ok {
   210  			i, err := strconv.ParseInt(s, 10, 64)
   211  			if err != nil {
   212  				return time.Time{}, util.TimeErr
   213  			}
   214  			v = i
   215  		}
   216  		switch v := v.(type) {
   217  		case float64:
   218  			return time.Unix(0, int64(math.Floor(v))).UTC(), nil
   219  		case int64:
   220  			return time.Unix(0, int64(v)).UTC(), nil
   221  		default:
   222  			return time.Time{}, util.TimeErr
   223  		}
   224  
   225  	// Special formats
   226  	case TimeFormatAuto:
   227  		switch s := v.(type) {
   228  		case string:
   229  			i, err := strconv.ParseInt(s, 10, 64)
   230  			if err == nil {
   231  				v = i
   232  				break
   233  			}
   234  			f, err := strconv.ParseFloat(s, 64)
   235  			if err == nil {
   236  				v = f
   237  				break
   238  			}
   239  
   240  			dates := []TimeFormat{
   241  				TimeFormat9, TimeFormat8,
   242  				TimeFormat6, TimeFormat5,
   243  				TimeFormat3, TimeFormat2, TimeFormat1,
   244  			}
   245  			for _, f := range dates {
   246  				t, err := f.Decode(s)
   247  				if err == nil {
   248  					return t, nil
   249  				}
   250  			}
   251  		}
   252  		switch v := v.(type) {
   253  		case float64:
   254  			if 0 <= v && v < 5373484.5 {
   255  				return TimeFormatJulianDay.Decode(v)
   256  			}
   257  			if v < 253402300800 {
   258  				return TimeFormatUnixFrac.Decode(v)
   259  			}
   260  			if v < 253402300800_000 {
   261  				return TimeFormatUnixMilli.Decode(v)
   262  			}
   263  			if v < 253402300800_000000 {
   264  				return TimeFormatUnixMicro.Decode(v)
   265  			}
   266  			return TimeFormatUnixNano.Decode(v)
   267  		case int64:
   268  			if 0 <= v && v < 5373485 {
   269  				return TimeFormatJulianDay.Decode(v)
   270  			}
   271  			if v < 253402300800 {
   272  				return TimeFormatUnixFrac.Decode(v)
   273  			}
   274  			if v < 253402300800_000 {
   275  				return TimeFormatUnixMilli.Decode(v)
   276  			}
   277  			if v < 253402300800_000000 {
   278  				return TimeFormatUnixMicro.Decode(v)
   279  			}
   280  			return TimeFormatUnixNano.Decode(v)
   281  		default:
   282  			return time.Time{}, util.TimeErr
   283  		}
   284  
   285  	case
   286  		TimeFormat2, TimeFormat2TZ,
   287  		TimeFormat3, TimeFormat3TZ,
   288  		TimeFormat4, TimeFormat4TZ,
   289  		TimeFormat5, TimeFormat5TZ,
   290  		TimeFormat6, TimeFormat6TZ,
   291  		TimeFormat7, TimeFormat7TZ:
   292  		s, ok := v.(string)
   293  		if !ok {
   294  			return time.Time{}, util.TimeErr
   295  		}
   296  		return f.parseRelaxed(s)
   297  
   298  	case
   299  		TimeFormat8, TimeFormat8TZ,
   300  		TimeFormat9, TimeFormat9TZ,
   301  		TimeFormat10, TimeFormat10TZ:
   302  		s, ok := v.(string)
   303  		if !ok {
   304  			return time.Time{}, util.TimeErr
   305  		}
   306  		t, err := f.parseRelaxed(s)
   307  		if err != nil {
   308  			return time.Time{}, err
   309  		}
   310  		return t.AddDate(2000, 0, 0), nil
   311  
   312  	default:
   313  		s, ok := v.(string)
   314  		if !ok {
   315  			return time.Time{}, util.TimeErr
   316  		}
   317  		if f == "" {
   318  			f = time.RFC3339Nano
   319  		}
   320  		return time.Parse(string(f), s)
   321  	}
   322  }
   323  
   324  func (f TimeFormat) parseRelaxed(s string) (time.Time, error) {
   325  	fs := string(f)
   326  	fs = strings.TrimSuffix(fs, "Z07:00")
   327  	fs = strings.TrimSuffix(fs, ".000")
   328  	t, err := time.Parse(fs+"Z07:00", s)
   329  	if err != nil {
   330  		return time.Parse(fs, s)
   331  	}
   332  	return t, nil
   333  }
   334  
   335  // Scanner returns a [database/sql.Scanner] that can be used as an argument to
   336  // [database/sql.Row.Scan] and similar methods to
   337  // decode a time value into dest using this format.
   338  func (f TimeFormat) Scanner(dest *time.Time) interface{ Scan(any) error } {
   339  	return timeScanner{dest, f}
   340  }
   341  
   342  type timeScanner struct {
   343  	*time.Time
   344  	TimeFormat
   345  }
   346  
   347  func (s timeScanner) Scan(src any) error {
   348  	var ok bool
   349  	var err error
   350  	if *s.Time, ok = src.(time.Time); !ok {
   351  		*s.Time, err = s.Decode(src)
   352  	}
   353  	return err
   354  }