github.com/snowflakedb/gosnowflake@v1.9.0/datetime_test.go (about)

     1  package gosnowflake
     2  
     3  import (
     4  	"testing"
     5  	"time"
     6  )
     7  
     8  func TestSnowflakeFormatToGoFormatUnitTest(t *testing.T) {
     9  	location, err := time.LoadLocation("Europe/Warsaw")
    10  	assertNilF(t, err)
    11  	someTime1 := time.Date(2024, time.January, 19, 3, 42, 33, 123456789, location)
    12  	someTime2 := time.Date(1973, time.December, 5, 13, 5, 3, 987000000, location)
    13  	testcases := []struct {
    14  		inputFormat string
    15  		output      string
    16  		formatted1  string
    17  		formatted2  string
    18  	}{
    19  		{
    20  			inputFormat: "YYYY-MM-DD HH24:MI:SS.FF TZH:TZM",
    21  			output:      "2006-01-02 15:04:05.000000000 Z07:00",
    22  			formatted1:  "2024-01-19 03:42:33.123456789 +01:00",
    23  			formatted2:  "1973-12-05 13:05:03.987000000 +01:00",
    24  		},
    25  		{
    26  			inputFormat: "YY-MM-DD HH12:MI:SS,FF5AM TZHTZM",
    27  			output:      "06-01-02 03:04:05,00000PM Z0700",
    28  			formatted1:  "24-01-19 03:42:33,12345AM +0100",
    29  			formatted2:  "73-12-05 01:05:03,98700PM +0100",
    30  		},
    31  		{
    32  			inputFormat: "MMMM DD, YYYY DY HH24:MI:SS.FF9 TZH:TZM",
    33  			output:      "January 02, 2006 Mon 15:04:05.000000000 Z07:00",
    34  			formatted1:  "January 19, 2024 Fri 03:42:33.123456789 +01:00",
    35  			formatted2:  "December 05, 1973 Wed 13:05:03.987000000 +01:00",
    36  		},
    37  		{
    38  			inputFormat: "MON DD, YYYY HH12:MI:SS,FF9PM TZH:TZM",
    39  			output:      "Jan 02, 2006 03:04:05,000000000PM Z07:00",
    40  			formatted1:  "Jan 19, 2024 03:42:33,123456789AM +01:00",
    41  			formatted2:  "Dec 05, 1973 01:05:03,987000000PM +01:00",
    42  		},
    43  		{
    44  			inputFormat: "HH24:MI:SS.FF3 HH12:MI:SS,FF9",
    45  			output:      "15:04:05.000 03:04:05,000000000",
    46  			formatted1:  "03:42:33.123 03:42:33,123456789",
    47  			formatted2:  "13:05:03.987 01:05:03,987000000",
    48  		},
    49  	}
    50  	for _, tc := range testcases {
    51  		t.Run(tc.inputFormat, func(t *testing.T) {
    52  			goFormat, err := snowflakeFormatToGoFormat(tc.inputFormat)
    53  			assertNilF(t, err)
    54  			assertEqualE(t, tc.output, goFormat)
    55  			assertEqualE(t, tc.formatted1, someTime1.Format(goFormat))
    56  			assertEqualE(t, tc.formatted2, someTime2.Format(goFormat))
    57  		})
    58  	}
    59  }
    60  
    61  func TestIncorrectSecondsFraction(t *testing.T) {
    62  	_, err := snowflakeFormatToGoFormat("HH24 MI SS FF")
    63  	assertHasPrefixE(t, err.Error(), "incorrect second fraction")
    64  }
    65  
    66  func TestSnowflakeFormatToGoFormatIntegrationTest(t *testing.T) {
    67  	runDBTest(t, func(dbt *DBTest) {
    68  		dbt.mustExec("ALTER SESSION SET TIME_OUTPUT_FORMAT = 'HH24:MI:SS.FF'")
    69  		dbt.mustExec("ALTER SESSION SET TIMESTAMP_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM'")
    70  		dbt.mustExec("ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF3'")
    71  		for _, forceFormat := range []string{forceJSON, forceARROW} {
    72  			dbt.mustExec(forceFormat)
    73  
    74  			for _, tc := range []struct {
    75  				sfType          string
    76  				formatParamName string
    77  				sfFunction      string
    78  			}{
    79  				{
    80  					sfType:          "TIMESTAMPLTZ",
    81  					formatParamName: "TIMESTAMP_OUTPUT_FORMAT",
    82  					sfFunction:      "CURRENT_TIMESTAMP",
    83  				},
    84  				{
    85  					sfType:          "TIMESTAMPTZ",
    86  					formatParamName: "TIMESTAMP_OUTPUT_FORMAT",
    87  					sfFunction:      "CURRENT_TIMESTAMP",
    88  				},
    89  				{
    90  					sfType:          "TIMESTAMPNTZ",
    91  					formatParamName: "TIMESTAMP_NTZ_OUTPUT_FORMAT",
    92  					sfFunction:      "CURRENT_TIMESTAMP",
    93  				},
    94  				{
    95  					sfType:          "DATE",
    96  					formatParamName: "DATE_OUTPUT_FORMAT",
    97  					sfFunction:      "CURRENT_DATE",
    98  				},
    99  				{
   100  					sfType:          "TIME",
   101  					formatParamName: "TIME_OUTPUT_FORMAT",
   102  					sfFunction:      "CURRENT_TIME",
   103  				},
   104  			} {
   105  				t.Run(tc.sfType+"___"+forceFormat, func(t *testing.T) {
   106  					params := dbt.mustQuery("show parameters like '" + tc.formatParamName + "'")
   107  					defer params.Close()
   108  					params.Next()
   109  					defaultTimestampOutputFormat, err := ScanSnowflakeParameter(params.rows)
   110  					assertNilF(t, err)
   111  
   112  					rows := dbt.mustQuery("SELECT " + tc.sfFunction + "()::" + tc.sfType + ", " + tc.sfFunction + "()::" + tc.sfType + "::varchar")
   113  					defer rows.Close()
   114  					var t1 time.Time
   115  					var t2 string
   116  					rows.Next()
   117  					err = rows.Scan(&t1, &t2)
   118  					assertNilF(t, err)
   119  					goFormat, err := snowflakeFormatToGoFormat(defaultTimestampOutputFormat.Value)
   120  					assertNilF(t, err)
   121  					assertEqualE(t, t1.Format(goFormat), t2)
   122  					parseResult, err := time.Parse(goFormat, t2)
   123  					assertNilF(t, err)
   124  					if tc.sfType != "TIME" {
   125  						assertEqualE(t, t1.UTC(), parseResult.UTC())
   126  					} else {
   127  						assertEqualE(t, t1.Hour(), parseResult.Hour())
   128  						assertEqualE(t, t1.Minute(), parseResult.Minute())
   129  						assertEqualE(t, t1.Second(), parseResult.Second())
   130  					}
   131  				})
   132  			}
   133  		}
   134  	})
   135  }