SNOW-2890156: Why does TimestampOption not apply to Time?

[Willem-J-an]

Is there a reason why TimestampOption does not apply to Time, or why there's no comparable option like TimeOption? It would seem to be the same reasons why this is helpful for timestamps also applies to time?

gosnowflake/converter.go

Lines 3299 to 3302 in b46142a

	case timeType:
	t = &arrow.Time64Type{Unit: arrow.Nanosecond}
	case timestampNtzType, timestampTzType:
	switch timestampOption {

[sfc-gh-pfus]

Hi Willem! Let me recap briefly why we introduced timestamp options. Arrow batches mode gives the possibility to consume native Arrow response from Snowflake. However, Arrow has its limitations - for instance, Arrow timestamp type is 64 bits. Based on the precision (secs/millis/micors/nanos) we are able to cover wider or narrower date range on 64bits. On the other hand, snowflake guarantees nanosecond precision from year 0001 to 9999 (who needs nanoseconds precision in 7 thousands years? 🤷 ). Due to this, timestamps are not returned with native arrow timestamp type, but the custom structure. In 99% cases you don't need this and this timestamp conversion is desired, but this option was introduced for this 1%.

Now, for time we don't have this problem - time always fits into arrow time type. Why are you asking? Do you want to have lower precision than nanoseconds?

[Willem-J-an]

We're doing Extract & Load with a philosophy of not doing transformations in this tool. Our target is Iceberg types in snowflake. That means we only support microsecond precision for all relevant types. My idea was to configure this timestampOption to ensure the timestamps align with this which should be okay, but for Time it is not possible. So we would have to transform it, which we prefer not to do.

In an ideal world, the types of the arrow records gosnowflake provides would align 100% with what is defined in snowflake, but unfortunately there are some gaps.

If you're interested in the gaps I've found now:

• time(6) is read with nanosecond precision
• FLOAT (Even FLOAT4) is returned as DOUBLE, so FLOAT32 type is never used, seems fine
• Decimals currently converted to double:
  • There is option to maintain precision WithHigherPrecision
    • Then Decimals are read as int with scale provided in metadata, we would need to convert that back to decimal
• ARRAY / OBJECT are retrieved as STRING, the WithStructuredTypesEnabled didn't help, possibly this doesn't work in the arrow reader route.
• TimestampTZ seems to lose the timezone info

[Willem-J-an]

@sfc-gh-pfus Is there a reason why timezone is not applied to timestampTzType? Is the timestamp value with timezone normalized to UTC?

gosnowflake/converter.go

Lines 3301 to 3326 in b46142a

	case timestampNtzType, timestampTzType:
	switch timestampOption {
	case UseOriginalTimestamp:
	converted = false
	case UseMicrosecondTimestamp:
	t = &arrow.TimestampType{Unit: arrow.Microsecond}
	case UseMillisecondTimestamp:
	t = &arrow.TimestampType{Unit: arrow.Millisecond}
	case UseSecondTimestamp:
	t = &arrow.TimestampType{Unit: arrow.Second}
	default:
	t = &arrow.TimestampType{Unit: arrow.Nanosecond}
	}
	case timestampLtzType:
	switch timestampOption {
	case UseOriginalTimestamp:
	converted = false
	case UseMicrosecondTimestamp:
	t = &arrow.TimestampType{Unit: arrow.Microsecond, TimeZone: loc.String()}
	case UseMillisecondTimestamp:
	t = &arrow.TimestampType{Unit: arrow.Millisecond, TimeZone: loc.String()}
	case UseSecondTimestamp:
	t = &arrow.TimestampType{Unit: arrow.Second, TimeZone: loc.String()}
	default:
	t = &arrow.TimestampType{Unit: arrow.Nanosecond, TimeZone: loc.String()}
	}

[sfc-gh-pfus]

Good call! Let me split my answer into bullets:

1. I understand your reasoning and it makes sense to me. If you are willing to implement this change soon, I suggest adding it yourself and contributing to our repository. Otherwise, it goes to our backlog, and I can't make promises on the timeline.
2. Re: gaps - I think it's a bit of discrepancy of Snowflake storage format (which is parquet, IIRC) and transfer format (Arrow). From driver perspective, we receive Arrow only and, when using arrow batches, we try to do as little transformations as possible. I agree that some of these mappings may be improved, maybe in v2? I added this to the candidate list. Re objects - structured types are still disabled on our backend for driver use, even if Go driver is ready for this.
3. Re: timezone - the code you showed creates only schema for arrow record. There is no global TZ for the whole record. Due to this, we can't set TZ in record, but the TZ is preserved in each value of this record separately.

[Willem-J-an]

Do you prefer to implement a separate timeOption to cover the units so it wouldn't break anyone relying on the existing timestampOption -> Time in ns behavior? I would assume so.

How would the TZ be preserved in each value? Doesn't that only happen when you stick to the custom type, but not when you use timestampOption to fallback to the normal arrow timestamp types? It seems my timestamp_tz is just converted to UTC in that case with no timezone (Except when my timestamp_tz was in a struct, then i get the corrected timestamp without timezone 😄 )

# definitions: 
"struct_value"         OBJECT,
"timestamp_value"      TIMESTAMP(6),
"timestamptz_value"     TIMESTAMP_TZ(6)

OBJECT_CONSTRUCT('timestamp_value', SYSDATE(), 'timestamptz_value', CONVERT_TIMEZONE('UTC', 'CET', SYSDATE())):
"{
  ""timestamp_value"": ""2025-12-04 08:31:36.665"",
  ""timestamptz_value"": ""2025-12-04 09:31:36.665""
}",
SYSDATE(): 2025-12-04 08:31:36.665,
CONVERT_TIMEZONE('UTC', 'CET', SYSDATE()): 2025-12-04 08:31:36.665

[sfc-gh-pfus]

Yes, backward compatibility is currently required. I'll think about reorganizing/merging those mentioned in v2.

Well, three things about sample you provided. First - you are talking about two different things, regular timestamps and timestamps embedded in structs. For now structs are returned as JSON strings, so the logic of timestampOption is not applied there.
Second - according to the doc this function returns NTZ flavour, so it's fine that this loses the timezone.
And third - I could reproduce this, when I ran SELECT SYSDATE(), CONVERT_TIMEZONE('UTC', 'CET', SYSDATE()) in Arrow Batches mode and printed the results using:

println(r.Column(0).ValueStr(0))
println(r.Column(1).ValueStr(0))

I got:

2025-12-05 06:13:54.105Z
2025-12-05 07:13:54.105Z

Which seems correct...