Wrong time zone in Snowflake Java UDTF

Question

In our Snowflake account, the time zone is set to America/New_York. There is a column in a table that is declared as DATE, so it does not store a time or time zone component. We pass values from that column into a Java UDTF, into a parameter declared as java.sql.Date. Let's assume that the DATE value is 2022-12-08.

A java.sql.Date represents a point in time, so we expected that the DATE column would be mapped as 2022-12-08 00:00:00.000 America/New_York, i.e., the original date value, at midnight, in the Snowflake account time zone. Instead, we are getting 2022-12-08 00:00:00.000 UTC, which in New York is a different date.

The Snowflake documentation says that the time zone setting is inherited from the database to the UDTF environment, so why is this happening? Why is a DATE converted to a java.sql.Date using the UTC time zone and not the Snowflake time zone?

To address some of the comments received here: we are using data types that are current, appropriate for our business, and supported according to Snowflake documentation: a Snowflake DATE column, which is appropriate because the time portion is not relevant to us, and which Snowflake maps to java.sql.Date when it passes the values to the UDTF. We are invoking a Java UDTF through a Snowflake function and therefore we have no control of how a Snowflake:DATE is passed in as a java.sql.Date.

We thought about converting the DATE to a TIMESTAMP_LTZ, so that we control the addition of a time/zone, but the TIMESTANP_LTZ maps to a java.sql.Timestamp, and we did not want to change the type of our UDTF input parameters (java.sql.Date).

Answer 1

This is not necessarily the best solution, but it's what we are doing as we wait for better ideas: we check each java.sql.Date value received and if it corresponds to "midnight in UTC" we transform it to "midnight in NYC on the same date". This works for us because the parameters to this UDTF are supposed to be dates (i.e. have no time/zone component) so if the time/zone are not "midnight in NYC" then they should be.

The clean solution would be for the Snowflake driver to offer a mapping from Snowflake:DATE (a data type without time/zone) to java.time.LocalDate, the obvious corresponding Java type. Until they do, we'll have to live with what is available: the DATE-to-java.sql.Date mapping.

public static ZonedDateTime zonedDateTimeUTC(Date date) {
    Instant instant = Instant.ofEpochMilli(date.getTime());
    return ZonedDateTime.ofInstant(instant, ZoneId.of("UTC"));
}
public static boolean isUTC(Date date) {
    ZonedDateTime zdt = zonedDateTimeUTC(date);
    return zdt.getHour() == 0 && zdt.getMinute() == 0 && zdt.getSecond() == 0 && zdt.getNano() == 0;
}
public static Date inNYC(Date date) {
    if (!isUTC(date)) return date;
    ZonedDateTime zdt = zonedDateTimeUTC(date);
    ZonedDateTime nyc = ZonedDateTime.of(zdt.getYear(), zdt.getMonthValue(), zdt.getDayOfMonth(),
            0, 0, 0, 0, ZoneId.of("America/New_York"));
    return new Date(nyc.toEpochSecond() * 1000L);
}