Package dev.frostlake.functions.scalar

Class SharedFunctionHelpers

java.lang.Object
dev.frostlake.functions.scalar.SharedFunctionHelpers
public final class SharedFunctionHelpers extends Object

  • Method Details

    • divideWithSnowflakeScale

      public static BigDecimal divideWithSnowflakeScale(BigDecimal dividend, BigDecimal divisor)
      Divide with Snowflake's result scale: max(S1, min(S1 + 6, 12)) where S1 is the dividend's scale (docs: "Arithmetic operators — division"), rounding half away from zero. Integer / integer therefore yields six fractional digits — 1/3 = 0.333333 — matching Snowflake output. Runtime values carry no declared column scale, so S1 is the value's carried scale, UNSTRIPPED — 10/2 keeps its six digits (5.000000) exactly as Snowflake renders it.

    • digest

      public static byte[] digest(String algo, byte[] input)

    • toHex

      public static String toHex(byte[] bytes)

    • temporalSqlLiteral

      public static String temporalSqlLiteral(Object value)
      A temporal value as a type-preserving SQL literal ('2026-01-03'::DATE), so a value inlined into regenerated SQL re-enters the engine as a temporal rather than a VARCHAR. Returns null for non-temporal values.

    • toUtf8

      public static byte[] toUtf8(Object v)
      The byte payload a function should hash, digest or encode: a BINARY value contributes its OWN bytes (never its hex rendering — BinaryValue.toString() is the display form, so toString().getBytes() would silently encode the ASCII hex digits and double the length), and everything else contributes the UTF-8 encoding of its Snowflake output text.

      The text is textOf(java.lang.Object) rather than toString() so a TIMESTAMP contributes 2024-01-02 03:04:05.000 — what Snowflake hashes — instead of java.time's 2024-01-02T03:04:05.

    • indexOfBytes

      public static long indexOfBytes(byte[] haystack, byte[] needle, int fromIndex)
      The 1-based position of needle in haystack at or after the 0-based fromIndex, or 0 when absent — the byte-wise counterpart of String.indexOf used by POSITION / CHARINDEX over BINARY values. An empty needle matches at fromIndex.

    • binaryArgBytes

      public static byte[] binaryArgBytes(Object value, String functionName)
      The byte payload of a BINARY-typed argument. Snowflake rejects a VARCHAR where BINARY is expected (no implicit text-to-binary coercion for these functions), so anything that is not an actual binary runtime value is an argument-type error.

    • isTruthy

      public static boolean isTruthy(Object v)

    • timestampText

      public static String timestampText(LocalDateTime dt)
      Snowflake's default TIMESTAMP output text — YYYY-MM-DD HH24:MI:SS.FF3: a SPACE separator and EXACTLY three fractional digits (…:38.604, …:28.000). LocalDateTime.toString instead used a T and dropped trailing zeros, so every text rendering of a timestamp (::VARCHAR, TO_VARCHAR, ||, a value embedded in a VARIANT) differed from Snowflake's.

    • yearText

      public static String yearText(int prolepticYear)
      A YEAR as the account prints it in every text of a date or a timestamp: the year of the era, four digits at least and never a sign — 20201 for a year past 9999, where java.time's own rendering is +20201, and 0002 for the second year before the first, where it is -0001 (live-verified over ::VARCHAR, TO_VARCHAR, || and the driver's text).
      Parameters:
      prolepticYear - the ISO year, 0 being the first year before the first
      Returns:
      the year's text

    • variantDateText

      public static String variantDateText(LocalDate date)
      A DATE's text inside a VARIANT: textOf(java.lang.Object)'s, except that a year before the first keeps its proleptic number, unpadded — live's VARIANT of the second year BC reads "-1-01-01" where its ::VARCHAR reads 0002-01-01, and one past 9999 reads "20201-01-15" (live-verified).
      Parameters:
      date - the date
      Returns:
      its text as a VARIANT string holds it

    • offsetTimestampText

      public static String offsetTimestampText(OffsetDateTime dt)
      A zone-carrying timestamp's default output text: the wall clock followed by its OFFSET. A zero offset is spelled Z, never +0000 — live prints 2020-01-01 18:00:00.000 Z for a TIMESTAMP_LTZ read under UTC, and the same Z for a literal written with one.

    • writtenOffsetTimestampText

      public static String writtenOffsetTimestampText(ZonedDateTime dt)
      A TIMESTAMP_TZ's output text: the wall clock it was WRITTEN with, followed by the offset it was written with. Unlike offsetTimestampText(java.time.OffsetDateTime) this never re-expresses — that is the whole difference between the two zoned types. Live: a value written 10:00:00 +0300 reads back 2020-01-01 10:00:00.000 +0300 under America/Los_Angeles and, unchanged, under UTC, while an LTZ holding the same instant follows the session.
      Parameters:
      dt - the value, whose zone is the fixed offset it carries
      Returns:
      the rendering, a zero offset spelled Z

    • offsetText

      public static String offsetText(ZoneOffset offset)
      Z at zero, else ±HHMM — Snowflake's offset spelling.

    • timeText

      public static String timeText(LocalTime t)
      Snowflake's default TIME output text — HH24:MI:SS, no fractional part.

    • textOf

      public static String textOf(Object value)
      A value rendered for a STRING context (::VARCHAR, TO_VARCHAR/TO_CHAR without a format, ||, embedding into a VARIANT): temporals use Snowflake's default output forms above, everything else its ordinary text. A DATE is YYYY-MM-DD with its era year — see yearText(int).

    • floatText

      public static String floatText(double d)
      Snowflake's FLOAT-to-VARCHAR rendering. Java's shortest-round-trip Double.toString printed 0.8999999761581421 where Snowflake prints 0.8999999762, so every SHA2/checksum over a stringified float diverged.

      The WIDTH is not one number. Live-verified across a magnitude ladder, the significant digits are ten below 10 and then one more per decade, capped at fourteen from 1e4 up: 

         1.414213562        10 sig   (and every smaller magnitude, down to 1.414213562e-16)
   14.142135624       11       141.421356237     12       1414.213562373    13
   14142.135623731    14       10686474581524.5  15       250000000000000   15 …

      THE CAP IS FIFTEEN, AND A LADDER BUILT ON SQRT(2) CANNOT SEE IT: that value's fifteenth significant digit is a zero, so its fourteen- and fifteen-digit renderings are the same text once trailing zeros are stripped. EXP(30) is what separates them — 10686474581524.5 is fifteen significant digits where fourteen would have dropped the .5.

      The notation follows from the width for free: %g turns scientific once the exponent reaches the precision, and with fifteen digits from 1e5 up that lands the plain range on an exponent of −4 through 14 — 2.5::FLOAT * 1e14 stays 250000000000000 and 1e15 is the first to leave, both live-verified.

    • toLocalDate

      public static LocalDate toLocalDate(Object v)

    • toLocalDateTime

      public static LocalDateTime toLocalDateTime(Object v)

    • toLocalDateTime

      public static LocalDateTime toLocalDateTime(Object v, boolean targetCarriesZone)
      The same reader, told whether the target CARRIES a zone. A TIMESTAMP_TZ or _LTZ moves the instant by the offset the text gives; a TIMESTAMP_NTZ or a DATE keeps the wall clock and drops it, which is what live does — '2020-01-01 10:00:00 +0300'::TIMESTAMP_NTZ is ten o'clock there, and the same text as a TIMESTAMP_TZ is seven o'clock UTC.

      Frostlake used to do neither: +0300 would not parse at all, and +03:00 parsed with the offset silently DISCARDED — so the value read back as a different instant, which is worse than the refusal beside it.

      The offset has nowhere to be KEPT while these are held as a naive LocalDateTime, so a TIMESTAMP_TZ is normalised to UTC rather than remembering that it was written +0300. Rendering it back the way live does is a separate piece of work.

    • toSessionOffsetDateTime

      public static OffsetDateTime toSessionOffsetDateTime(Object v)
      A TIMESTAMP_LTZ value: the INSTANT the input names, expressed at the SESSION zone's offset for that instant. Live proves it is an instant and not a wall clock, because the same stored value re-renders when the session zone changes — 2020-01-01 10:00:00 -0800 under America/Los_Angeles is 2020-01-01 18:00:00 Z under UTC, digits and all.

      Text WITHOUT an offset names a wall clock in the session's zone; text WITH one already fixes the instant and is merely re-expressed. The offset is the zone's AT THAT INSTANT, so a January value and a June value in the same zone carry different ones.

    • toWrittenOffsetTimestamp

      public static ZonedDateTime toWrittenOffsetTimestamp(Object v)
      A TIMESTAMP_TZ value: the instant the input names, kept at the offset it was WRITTEN with.

      THIS IS WHAT SEPARATES THE TWO ZONED TYPES. An LTZ is an instant that re-renders in whatever zone the session currently has; a TZ remembers how it was spelled and never moves. Both agree on the instant underneath — 10:00 +0300 really is 07:00 UTC, and comparisons, ordering and EPOCH all read it that way — so the offset is presentation, and only presentation.

      Text carrying NO offset takes the session zone's offset AT THAT INSTANT, and then that offset is fixed: the same literal cast under America/Los_Angeles reads -0800 and under UTC reads Z, each staying put afterwards. The value is held as a ZonedDateTime whose zone IS that offset, which is what distinguishes it from the OffsetDateTime an LTZ carries.

      Parameters:
      v - the text, or an already-temporal value to re-flavour
      Returns:
      the TZ value, or null for null input

    • sameTimestampFlavour

      public static Object sameTimestampFlavour(Object source, LocalDateTime computed)
      A computed timestamp given back in the FLAVOUR of the value it was computed from: an LTZ input yields an LTZ result, so DATEADD(day, 1, tl) keeps its offset instead of decaying to a TIMESTAMP_NTZ, and a TZ input keeps the offset it was written with. The wall clock is what the caller computed; only the flavour is restored.

    • toLocalTime

      public static LocalTime toLocalTime(Object v)

    • atDeclaredPrecision

      public static Object atDeclaredPrecision(Object value, int precision)
      A temporal value truncated to a declared fractional precision — the digits past it are dropped, never rounded — for the TIME and TIMESTAMP flavours alike. A DATE, a precision of nine (or none) and any non-temporal value pass through untouched.
      Parameters:
      value - the temporal value, in any of the Java carriers
      precision - the declared fractional-second digits, 0 to 9
      Returns:
      the value at that precision

    • toTemporalValue

      public static Object toTemporalValue(String baseTypeUpper, Object value)

    • epochToLocalDateTime

      public static LocalDateTime epochToLocalDateTime(long epoch)
      Interpret a value as a Unix-epoch instant, picking the unit by magnitude the way Snowflake does for a STRING argument containing an integer: |v| < 31,536,000,000 → seconds, else milliseconds, else microseconds, else nanoseconds. Returns the wall-clock time at UTC. A NUMERIC TO_TIMESTAMP argument must NOT go through this detection — it is always seconds (see parseTimestampWithFormatOrScale(java.lang.Object, java.lang.Object)).

    • epochDecimalToLocalDateTime

      public static LocalDateTime epochDecimalToLocalDateTime(BigDecimal seconds)
      Interpret a numeric TO_TIMESTAMP argument at an explicit scale: the value counts 10-scale-second units since the Unix epoch (scale 0 = seconds — Snowflake's default for a NUMERIC argument, however large — 3 = milliseconds, 9 = nanoseconds). Returns the wall-clock time at UTC.

    • epochAtScaleToLocalDateTime

      public static LocalDateTime epochAtScaleToLocalDateTime(long epoch, int scale)

    • isExplicitFormat

      public static boolean isExplicitFormat(String format)
      Whether a TO_DATE / TO_TIME / TO_TIMESTAMP format argument names an explicit model: anything but the word AUTO, in any case — the empty string included, which is a model that matches nothing, so every input is refused under it (live-verified: TO_DATE('2020-01-15', '') is "Can't parse '2020-01-15' as date with format ''").

    • parseTimestampWithFormatOrScale

      public static LocalDateTime parseTimestampWithFormatOrScale(Object value, Object formatOrScale)

    • parseTimestampWithFormat

      public static LocalDateTime parseTimestampWithFormat(Object value, String format)
      Parse a value to a LocalDateTime for TO_TIMESTAMP: a numeric value is a Unix epoch in SECONDS (magnitude-based unit detection applies only to a string of digits — live: TO_TIMESTAMP_NTZ(1631711999000) is year 53676 while TO_TIMESTAMP_NTZ('1631711999000') is 2021-09-15); a string is read against an explicit Snowflake format when one is supplied — a mismatch is the account's own refusal, "Can't parse '<input>' as timestamp with format '<format>'" — under AUTO a string of digits is a magnitude-detected epoch (epochToLocalDateTime(long)), and any other string is read flexibly via toLocalDateTime(java.lang.Object). An offset the model reads is dropped: this is the NTZ wall clock.

    • parseDateWithFormat

      public static LocalDate parseDateWithFormat(Object value, String format)
      Parse a value to a LocalDate for TO_DATE: a numeric value is a Unix epoch (its date part); a string is read against an explicit Snowflake format when one is supplied (a mismatch is "Can't parse '<input>' as date with format '<format>'"), else via toLocalDate(java.lang.Object).

    • datePart

      public static long datePart(String unitRaw, LocalTime time)
      A component of a TIME. A time of day carries no date, so only the clock components are readable; the day-or-larger units are refused before this is reached (they cannot be decided from a value, because a refusal raised here would never fire over zero rows).
      Parameters:
      unitRaw - the component as written
      time - the time of day
      Returns:
      the component's value

    • datePart

      public static long datePart(String unitRaw, LocalDateTime dt)

    • datePart

      public static long datePart(String unitRaw, LocalDateTime dt, Object source, String functionName)
      datePart(String, LocalDateTime, String) given the ORIGINAL value as well as its wall clock, which two families of component need: the EPOCH ones read the instant, and the TIMEZONE ones read the offset itself.
      Parameters:
      unitRaw - the component as written
      dt - the wall clock
      source - the value the wall clock came from, or null when there is none to consult
      functionName - the function to name in a refusal
      Returns:
      the component's value

    • datePart

      public static long datePart(String unitRaw, LocalDateTime dt, String functionName)
      A component of a timestamp, reported for functionName when the word names none.

      EXTRACT and DATE_PART share ONE vocabulary — measured word by word down the whole abbreviation ladder, and identical at every rung, which is not what either function's own history suggested. What differs is only the sentence: each names ITSELF as the parameter, and the word is quoted exactly as it arrived. DATE_PART's bareword is upper-cased before it gets here (its argument is a unit SLOT), so DATE_PART(ss, …) reports [SS] while DATE_PART('ss', …) and EXTRACT(ss FROM …) both report [ss].

    • parseDayOfWeek

      public static DayOfWeek parseDayOfWeek(String name)

    • stripPluralS

      public static String stripPluralS(String upper)
      unit.toUpperCase() with one trailing S removed — the plural strip without a per-call regex compile.

    • invalidDatePart

      public static RuntimeException invalidDatePart(String unitRaw, String functionName)
      The bad-component refusal EXTRACT and DATE_PART share, each naming itself as the parameter.

    • notADateTimeComponent

      public static RuntimeException notADateTimeComponent(Object raw, String functionName)
      The refusal for a word that is not a date/time component of the function that was given it. The account quotes the word as the CALL wrote it — a bareword arrives already upper-cased, a quoted literal keeps its own case — so the text is passed through rather than folded here.
      Parameters:
      raw - the unit word as it reached the function
      functionName - the function reporting it
      Returns:
      the refusal to throw

    • notAComponentOfType

      public static RuntimeException notAComponentOfType(Object raw, String functionName, String typeName)
      The refusal for a unit that is not a component of the VALUE's type — a day or larger asked of a TIME, which carries no date to truncate. The account spells this one differently from the unit-not-known refusal beside it: the word is bracketed WITHOUT quotes, upper-cased whatever the call wrote, and the sentence names the type as well as the function.
      Parameters:
      raw - the unit word as written
      functionName - the function reporting it
      typeName - the argument's type
      Returns:
      the refusal to throw

    • isComponentOnlyUnit

      public static boolean isComponentOnlyUnit(Object raw)
      Whether a word names a date COMPONENT that no interval can be measured in. woy and weekofyear are units for INTERVAL and readable by DATE_PART, but DATEADD, DATEDIFF and DATE_TRUNC all refuse them — the one place the two vocabularies disagree.
      Parameters:
      raw - the unit word as written
      Returns:
      true when an interval function must refuse it

    • canonicalDateUnit

      public static String canonicalDateUnit(Object raw)