Repository: snowflakedb/gosnowflake
Pull request #1077 (2024-03-20 16:44:50)

SNOW-1256926 Add converter from snowflake date/time format to go
### Description
Provided converter from snowflake date time formats to golang formats

### Checklist
- [ ] Code compiles correctly
- [ ] Run ``make fmt`` to fix inconsistent formats
- [ ] Run ``make lint`` to get lint errors and fix all of them
- [ ] Created tests which fail without the change (if possible)
- [ ] All tests passing
- [ ] Extended the README / documentation, if necessary


codecov[bot]: ## [Codecov](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1077?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb) Report
Attention: Patch coverage is `80.95238%` with `4 lines` in your changes are missing coverage. Please review.
> Project coverage is 81.81%. Comparing base [(`3a5605d`)](https://app.codecov.io/gh/snowflakedb/gosnowflake/commit/3a5605dd2651d269d27776e09e5f33f1982dd348?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb) to head [(`eff7f89`)](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1077?dropdown=coverage&src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb).

| [Files](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1077?dropdown=coverage&src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb) | Patch % | Lines |
|---|---|---|
| [datetime.go](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1077?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb#diff-ZGF0ZXRpbWUuZ28=) | 80.95% | [4 Missing :warning: ](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1077?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb) |

<details><summary>Additional details and impacted files</summary>


```diff
@@            Coverage Diff             @@
##           master    #1077      +/-   ##
==========================================
- Coverage   84.05%   81.81%   -2.25%     
==========================================
  Files          50       50              
  Lines        9954     9974      +20     
==========================================
- Hits         8367     8160     -207     
- Misses       1587     1814     +227     
```



</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/snowflakedb/gosnowflake/pull/1077?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb).   
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=snowflakedb).


Patch:
diff --git a/datetime.go b/datetime.go
new file mode 100644
index 0000000..222bae5
--- /dev/null
+++ b/datetime.go
@@ -0,0 +1,62 @@
+// Copyright (c) 2024 Snowflake Computing Inc. All rights reserved.
+
+package gosnowflake
+
+import (
+	"errors"
+	"regexp"
+	"strconv"
+	"strings"
+)
+
+var incorrectSecondsFractionRegex = regexp.MustCompile(`[^.,]FF`)
+var correctSecondsFractionRegex = regexp.MustCompile(`FF(?P<fraction>\d?)`)
+
+type formatReplacement struct {
+	input  string
+	output string
+}
+
+var formatReplacements = []formatReplacement{
+	{input: "YYYY", output: "2006"},
+	{input: "YY", output: "06"},
+	{input: "MMMM", output: "January"},
+	{input: "MM", output: "01"},
+	{input: "MON", output: "Jan"},
+	{input: "DD", output: "02"},
+	{input: "DY", output: "Mon"},
+	{input: "HH24", output: "15"},
+	{input: "HH12", output: "03"},
+	{input: "AM", output: "PM"},
+	{input: "MI", output: "04"},
+	{input: "SS", output: "05"},
+	{input: "TZH", output: "Z07"},
+	{input: "TZM", output: "00"},
+}
+
+func snowflakeFormatToGoFormat(sfFormat string) (string, error) {
+	res := sfFormat
+	for _, replacement := range formatReplacements {
+		res = strings.Replace(res, replacement.input, replacement.output, -1)
+	}
+
+	if incorrectSecondsFractionRegex.MatchString(res) {
+		return "", errors.New("incorrect second fraction - golang requires fraction to be preceded by comma or decimal point")
+	}
+	for {
+		submatch := correctSecondsFractionRegex.FindStringSubmatch(res)
+		if submatch == nil {
+			break
+		}
+		fractionNumbers := 9
+		if submatch[1] != "" {
+			var err error
+			fractionNumbers, err = strconv.Atoi(submatch[1])
+			if err != nil {
+				return "", err
+			}
+		}
+		res = strings.Replace(res, submatch[0], strings.Repeat("0", fractionNumbers), -1)
+	}
+	return res, nil
+}


Test patch:
diff --git a/datetime_test.go b/datetime_test.go
new file mode 100644
index 0000000..5fbf4de
--- /dev/null
+++ b/datetime_test.go
@@ -0,0 +1,133 @@
+package gosnowflake
+
+import (
+	"testing"
+	"time"
+)
+
+func TestSnowflakeFormatToGoFormatUnitTest(t *testing.T) {
+	location, err := time.LoadLocation("Europe/Warsaw")
+	assertNilF(t, err)
+	someTime1 := time.Date(2024, time.January, 19, 3, 42, 33, 123456789, location)
+	someTime2 := time.Date(1973, time.December, 5, 13, 5, 3, 987000000, location)
+	testcases := []struct {
+		inputFormat string
+		output      string
+		formatted1  string
+		formatted2  string
+	}{
+		{
+			inputFormat: "YYYY-MM-DD HH24:MI:SS.FF TZH:TZM",
+			output:      "2006-01-02 15:04:05.000000000 Z07:00",
+			formatted1:  "2024-01-19 03:42:33.123456789 +01:00",
+			formatted2:  "1973-12-05 13:05:03.987000000 +01:00",
+		},
+		{
+			inputFormat: "YY-MM-DD HH12:MI:SS,FF5AM TZHTZM",
+			output:      "06-01-02 03:04:05,00000PM Z0700",
+			formatted1:  "24-01-19 03:42:33,12345AM +0100",
+			formatted2:  "73-12-05 01:05:03,98700PM +0100",
+		},
+		{
+			inputFormat: "MMMM DD, YYYY DY HH24:MI:SS.FF9 TZH:TZM",
+			output:      "January 02, 2006 Mon 15:04:05.000000000 Z07:00",
+			formatted1:  "January 19, 2024 Fri 03:42:33.123456789 +01:00",
+			formatted2:  "December 05, 1973 Wed 13:05:03.987000000 +01:00",
+		},
+		{
+			inputFormat: "MON DD, YYYY HH12:MI:SS,FF9PM TZH:TZM",
+			output:      "Jan 02, 2006 03:04:05,000000000PM Z07:00",
+			formatted1:  "Jan 19, 2024 03:42:33,123456789AM +01:00",
+			formatted2:  "Dec 05, 1973 01:05:03,987000000PM +01:00",
+		},
+		{
+			inputFormat: "HH24:MI:SS.FF3 HH12:MI:SS,FF9",
+			output:      "15:04:05.000 03:04:05,000000000",
+			formatted1:  "03:42:33.123 03:42:33,123456789",
+			formatted2:  "13:05:03.987 01:05:03,987000000",
+		},
+	}
+	for _, tc := range testcases {
+		t.Run(tc.inputFormat, func(t *testing.T) {
+			goFormat, err := snowflakeFormatToGoFormat(tc.inputFormat)
+			assertNilF(t, err)
+			assertEqualE(t, tc.output, goFormat)
+			assertEqualE(t, tc.formatted1, someTime1.Format(goFormat))
+			assertEqualE(t, tc.formatted2, someTime2.Format(goFormat))
+		})
+	}
+}
+
+func TestIncorrectSecondsFraction(t *testing.T) {
+	_, err := snowflakeFormatToGoFormat("HH24 MI SS FF")
+	assertHasPrefixE(t, err.Error(), "incorrect second fraction")
+}
+
+func TestSnowflakeFormatToGoFormatIntegrationTest(t *testing.T) {
+	runDBTest(t, func(dbt *DBTest) {
+		dbt.mustExec("ALTER SESSION SET TIME_OUTPUT_FORMAT = 'HH24:MI:SS.FF'")
+		for _, forceFormat := range []string{forceJSON, forceARROW} {
+			dbt.mustExec(forceFormat)
+
+			for _, tc := range []struct {
+				sfType          string
+				formatParamName string
+				sfFunction      string
+			}{
+				{
+					sfType:          "TIMESTAMPLTZ",
+					formatParamName: "TIMESTAMP_OUTPUT_FORMAT",
+					sfFunction:      "CURRENT_TIMESTAMP",
+				},
+				{
+					sfType:          "TIMESTAMPTZ",
+					formatParamName: "TIMESTAMP_OUTPUT_FORMAT",
+					sfFunction:      "CURRENT_TIMESTAMP",
+				},
+				{
+					sfType:          "TIMESTAMPNTZ",
+					formatParamName: "TIMESTAMP_NTZ_OUTPUT_FORMAT",
+					sfFunction:      "CURRENT_TIMESTAMP",
+				},
+				{
+					sfType:          "DATE",
+					formatParamName: "DATE_OUTPUT_FORMAT",
+					sfFunction:      "CURRENT_DATE",
+				},
+				{
+					sfType:          "TIME",
+					formatParamName: "TIME_OUTPUT_FORMAT",
+					sfFunction:      "CURRENT_TIME",
+				},
+			} {
+				t.Run(tc.sfType+"___"+forceFormat, func(t *testing.T) {
+					params := dbt.mustQuery("show parameters like '" + tc.formatParamName + "'")
+					defer params.Close()
+					params.Next()
+					defaultTimestampOutputFormat, err := ScanSnowflakeParameter(params.rows)
+					assertNilF(t, err)
+
+					rows := dbt.mustQuery("SELECT " + tc.sfFunction + "()::" + tc.sfType + ", " + tc.sfFunction + "()::" + tc.sfType + "::varchar")
+					defer rows.Close()
+					var t1 time.Time
+					var t2 string
+					rows.Next()
+					err = rows.Scan(&t1, &t2)
+					assertNilF(t, err)
+					goFormat, err := snowflakeFormatToGoFormat(defaultTimestampOutputFormat.Value)
+					assertNilF(t, err)
+					assertEqualE(t, t1.Format(goFormat), t2)
+					parseResult, err := time.Parse(goFormat, t2)
+					assertNilF(t, err)
+					if tc.sfType != "TIME" {
+						assertEqualE(t, t1.UTC(), parseResult.UTC())
+					} else {
+						assertEqualE(t, t1.Hour(), parseResult.Hour())
+						assertEqualE(t, t1.Minute(), parseResult.Minute())
+						assertEqualE(t, t1.Second(), parseResult.Second())
+					}
+				})
+			}
+		}
+	})
+}