SQLite Forum

Feature Request: more flexible timestring parsing

Feature Request: more flexible timestring parsing

(1) By Adam S Levy (alaskanarcher) on 2020-05-09 03:22:00 [link] [source]

Time strings come in a wide range of formats many of which do not conform to ISO-8601.

I propose an additional time parsing function or modifier that allows for more flexible parsing of time strings. I suggest modeling it from Golang's time.Parse function. This allows a user to write a specific time stamp in the desired format:

  Mon Jan 2 15:04:05 -0700 MST 2006

The implementation and docs for this method of parsing can be found here:



(2) By anonymous on 2020-05-09 03:27:33 in reply to 1 [source]

It is a perfect opportunity for an extension. It might be interesting (or worse) to see a SQLite3 extension written in Go. Does it support DLL creation?

(3) By Adam S Levy (alaskanarcher) on 2020-05-09 22:04:22 in reply to 2 [link] [source]

I believe it does. But I'm more knowledgeable about calling C code from Go than building Go code to be linked for another lib.

The Go package could be very simple.

package timeparse

import "time"

func Parse(layout, value string) (string, error) {
        t, err := time.Parse(layout, value)
        if err != nil {
                return "", err
        return t.Unix().Format(`2006-01-02T15:04:05.999999999Z`), nil

(4) By Erik (nelson2005) on 2020-05-11 15:22:44 in reply to 1 [link] [source]

I'm not sure if it meets your use-case, but I used the single-file strptime code to support user-defined formats, hooked in as a SQLite user-defined function. It was easy and worked quite well for what I was doing.

(5) By Adam S Levy (alaskanarcher) on 2020-05-15 21:52:27 in reply to 4 [link] [source]

Is this something that can readily be done from within the CLI interface?

(6) By Larry Brasfield (LarryBrasfield) on 2020-05-16 01:45:46 in reply to 5 [link] [source]

The CLI interface has a .load command with which to load extensions. It also has a -init FILENAME invocation option which can be used to run a set of commands on startup. With those alone, you could get the functionality you seek if you are willing to write the extension. It is reasonably easy to do.

There are some extensions which are build into the shell, but they are structured and initialized as if they resided in a just-loaded DLL. If you wanted to build an extension of your own into a customized shell, that too would be simple to do, with examples of similar "modification" already in the distribution source.

(7) By David Jones (vman59) on 2020-05-16 21:39:43 in reply to 4 [link] [source]

I did something similar, but the extension uses a separate function to set the format string so I don't have to include it explicitly with every invocation of the parse function. I then made the config function an eponymous virtual table so redefining the format is an update to a table rather that a side effect of a function call.