gsql_ts_to_epoch_seconds_signed()

Converts a timestamp in canonical string format to Unix epoch time (seconds before or after Jan 1, 1970). It supports dates from 0001-01-01 to 9999-12-31, returning negative epoch values for dates prior to 1970. Invalid or out-of-range dates (e.g., Feb 30th) return defaultTimestamp.

We also have another timestamp function, gsql_ts_to_epoch_seconds(), which converts a timestamp to Unix epoch time (seconds since Jan 1st, 1970) and returns an unsigned integer (UINT). For backward compatibility behavior (3.10.3 or earlier), consider gsql_ts_to_epoch_seconds_legacy().

Syntax

gsql_ts_to_epoch_seconds_signed(timestamp, defaultTimestamp = 0)

Parameters

timestamp

Required. The timestamp to convert to Unix epoch time. The timestamp parameter should be in one of the following formats:

  • "%Y-%m-%d %H:%M:%S"

  • "%Y/%m/%d %H:%M:%S"

  • "%Y-%m-%dT%H:%M:%S.000z"

    • Text after the dot . is ignored

defaultTimestamp

Optional. If the timestamp is invalid (e.g., out of range or structurally incorrect), it returns the provided default value. The default is 0.

Return type

int64_t

  • Returns the epoch seconds for valid timestamps between 0001-01-01 00:00:00 and 9999-12-31 23:59:59.

  • Returns defaultTimestamp for invalid times (impossible dates or out-of-range).

Timestamps before 1583 use the Gregorian calendar algorithm, so accuracy cannot be guaranteed. Using dates before 1583 is not recommended.

Example

// Example with a valid date after 1970
gsql_ts_to_epoch_seconds_signed("2022-06-30 22:08:28") -> 1656652105

// Example with a date before 1970 (returns a negative epoch value)
gsql_ts_to_epoch_seconds_signed("1969-12-31 23:59:59") -> -1

// Example with a date from year 0001
gsql_ts_to_epoch_seconds_signed("0001-01-01 00:00:00") -> -62135596800

// Example with an invalid date (returns default 0) gsql_ts_to_epoch_seconds_signed("2022-02-30 00:00:00") -> 0

Choose this function if you need to support negative epoch values for pre-1970 dates, process a broader range of historical data, or require a signed integer output.