解析时间为HHMMSS00格式

Question

I'm working with some data from multiple sources and one of these sources is a Sage ERP system.

I am trying to reference two files in Sage in particular, an audit date and audit time (AUDTDATE and AUDTTIME).

I need to parse this and store it as a DATETIME in a Microsoft SQL Server database.

Currently, I am just trying to figure out the best way to parse this.

An example of what the data might look like is below:

+----------+----------+
| AUDTDATE | AUDTTIME |
+----------+----------+
| 20170228 |  5013756 |
+----------+----------+

AUDTDATE is a yyyymmdd format and the AUDTTIME is HHMMSS00.

So I tried the below as a test:

func main() {
    value := "20170228 5013756"
    layout := "20060102 15040500"

    t, _ := time.Parse(layout, value)
    fmt.Println(t)
}

This doesn't work, it just returns 0001-01-01 00:00:00 +0000 UTC when run.

If I change the time to this 050137 and the layout to 150405 then this works fine:

func main() {
    value := "20170228 050137"
    layout := "20060102 150405"

    t, _ := time.Parse(layout, value)
    fmt.Println(t)
}

One way that I can think of to deal with this is to strip the milliseconds off from the end and then check the length and add a zero to the beginning if it needs one.

This seems like a pretty ugly solution and would involve doing something like this:

func main() {
    date := "20170228"
    timeString := "5013756"
    value := date + prepareTime(timeString)
    layout := "20060102150405"

    t, _ := time.Parse(layout, value)
    fmt.Println(t)
}

func prepareTime(time string) string {
    if len(time) == 7 {
        time = "0" + time
    }
    return time[:6]
}

Is there a way to do this without going through the above? Perhaps natively with the time package?

Answer 1

Assuming that you're pulling back 2 separate values from the DB, you can use fmt.Sprintf to 0 pad timeString. Combining it with the date string, you can use the following:

value := fmt.Sprintf("%s %08s", date, timeString)[:15]

In your code:

func main() {
    date := "20170228"
    timeString := "5013756"
    value := fmt.Sprintf("%s %08s", date, timeString)[:15]
    layout := "20060102 150405"

    t, _ := time.Parse(layout, value)
    fmt.Println(t)
}

Results:

2017-02-28 05:01:37 +0000 UTC

This approach is useful because it will also correctly pad any shorter value of time, e.g. 13756 will be converted to 00013756.

The fmt.Sprintf function is useful to format arguments into a string using the formatting you desire as specified by a format string and a list of arguments (...interface{}). The format string tells the function how to render the arguments.

This format string uses two items of note:

• String verb (%s): The format string uses a variety of verbs that are used for string substitutions. %s is specifically to render a string or a slice. Other popular verbs include %d for base 10 integer and %f for float with a complete list in the docs. The %v verb is very useful can also be used here as it will render an argument's default value.
• 0 left padding: To 0 left pad an argument, use 0 followed by a length number in the verb after the %. This will prepended the argument with a maximum number of 0s specified in the length number. For example, %08s will render a string with up to 8 prepended zeros. This means a string "" will be "00000000" while a string "1234567" will result in "01234567". If the string is longer than the length, nothing will be prepended.

From the documentation:

%s  the uninterpreted bytes of the string or slice

0   pad with leading zeros rather than spaces;
    for numbers, this moves the padding after the sign

More detailed is available in the documentation: https://golang.org/pkg/fmt/