org.apache.flink.table.runtime.functions

Class SqlDateTimeUtils

java.lang.Object

org.apache.flink.table.runtime.functions.SqlDateTimeUtils

public class SqlDateTimeUtils
extends Object

Utility functions for datetime types: date, time, timestamp. Currently, it is a bit messy putting date time functions in various classes because the runtime module does not depend on calcite..

Field Summary

Fields

Modifier and Type	Field and Description
static TimeZone	UTC_ZONE The UTC time zone.

Constructor Summary

Constructors

Constructor and Description
SqlDateTimeUtils()

Method Summary

Modifier and Type	Method and Description
static String	convertTz(String dateStr, String tzFrom, String tzTo)
static String	convertTz(String dateStr, String format, String tzFrom, String tzTo) Convert datetime string from a time zone to another time zone.
static String	dateAdd(long ts, int days)
static String	dateAdd(long ts, int days, TimeZone tz) Do addition on timestamp.
static String	dateAdd(String dateStr, int days)
static String	dateAdd(String dateStr, int days, TimeZone tz) Do addition on date string.
static int	dateDiff(long t1, long t2)
static int	dateDiff(long t1, long t2, TimeZone tz) NOTE: (1).
static int	dateDiff(long t1, String t2Str)
static int	dateDiff(long t1, String t2Str, TimeZone tz)
static int	dateDiff(String t1Str, long t2)
static int	dateDiff(String t1Str, long t2, TimeZone tz)
static int	dateDiff(String t1Str, String t2Str)
static int	dateDiff(String t1Str, String t2Str, TimeZone tz)
static String	dateFormat(long ts, String format)
static String	dateFormat(long ts, String format, TimeZone tz) Format a timestamp as specific.
static String	dateFormat(String dateStr, String toFormat)
static String	dateFormat(String dateStr, String fromFormat, String toFormat)
static String	dateFormat(String dateStr, String fromFormat, String toFormat, TimeZone tz) Format a string datetime as specific.
static String	dateFormat(String dateStr, String toFormat, TimeZone tz)
static String	dateFormat(TimestampData ts, String format)
static String	dateFormat(TimestampData ts, String format, TimeZone zone)
static String	dateFormat(TimestampData ts, String format, java.time.ZoneId zoneId) Format a timestamp as specific.
static String	dateFormatTz(long ts, String tzStr)
static String	dateFormatTz(long ts, String format, String tzStr)
static Integer	dateStringToUnixDate(String s)
static String	dateSub(long ts, int days)
static String	dateSub(long ts, int days, TimeZone tz) Do subtraction on date string.
static String	dateSub(String dateStr, int days)
static String	dateSub(String dateStr, int days, TimeZone tz) Do subtraction on date string.
static int	dateToInternal(Date date) Converts the Java type used for UDF parameters of SQL DATE type (Date) to internal representation (int).
static long	dateToTimestampWithLocalZone(int date, TimeZone tz)
static long	extractFromTimestamp(org.apache.calcite.avatica.util.TimeUnitRange range, TimestampData ts, TimeZone tz)
static int	extractYearMonth(org.apache.calcite.avatica.util.TimeUnitRange range, int v)
static long	fromTimestamp(long ts)
static String	fromUnixtime(DecimalData unixtime)
static String	fromUnixtime(DecimalData unixtime, TimeZone tz)
static String	fromUnixtime(double unixtime)
static String	fromUnixtime(double unixtime, TimeZone tz)
static String	fromUnixtime(long unixtime)
static String	fromUnixtime(long unixtime, String format)
static String	fromUnixtime(long unixtime, String format, TimeZone tz) Convert unix timestamp (seconds since '1970-01-01 00:00:00' UTC) to datetime string in the given format.
static String	fromUnixtime(long unixtime, TimeZone tz) Convert unix timestamp (seconds since '1970-01-01 00:00:00' UTC) to datetime string in the "yyyy-MM-dd HH:mm:ss" format.
static Date	internalToDate(int v) Converts the internal representation of a SQL DATE (int) to the Java type used for UDF parameters (Date).
static Time	internalToTime(int v) Converts the internal representation of a SQL TIME (int) to the Java type used for UDF parameters (Time).
static Timestamp	internalToTimestamp(long v) Converts the internal representation of a SQL TIMESTAMP (long) to the Java type used for UDF parameters (Timestamp).
static long	localDateTimeToUnixTimestamp(java.time.LocalDateTime dateTime)
static int	localDateToUnixDate(java.time.LocalDate date)
static int	localTimeToUnixDate(java.time.LocalTime time)
static int	strToDate(String dateStr, String fromFormat) Returns the epoch days since 1970-01-01.
static long	timestampCeil(org.apache.calcite.avatica.util.TimeUnitRange range, long ts)
static long	timestampCeil(org.apache.calcite.avatica.util.TimeUnitRange range, long ts, TimeZone tz) Keep the algorithm consistent with Calcite DateTimeUtils.julianDateFloor, but here we take time zone into account.
static long	timestampFloor(org.apache.calcite.avatica.util.TimeUnitRange range, long ts)
static long	timestampFloor(org.apache.calcite.avatica.util.TimeUnitRange range, long ts, TimeZone tz)
static long	timestampToInternal(Timestamp ts) Converts the Java type used for UDF parameters of SQL TIMESTAMP type (Timestamp) to internal representation (long).
static String	timestampToString(long ts, int precision)
static String	timestampToString(long ts, int precision, TimeZone tz) Convert a timestamp to string.
static String	timestampToString(TimestampData ts, int precision)
static String	timestampToString(TimestampData ts, TimeZone tz, int precision)
static TimestampData	timestampToTimestampWithLocalZone(TimestampData ts, TimeZone tz)
static int	timestampWithLocalZoneToDate(long ts, TimeZone tz)
static int	timestampWithLocalZoneToTime(long ts, TimeZone tz)
static TimestampData	timestampWithLocalZoneToTimestamp(TimestampData ts, TimeZone tz)
static Integer	timeStringToUnixDate(String v)
static Integer	timeStringToUnixDate(String v, int start)
static int	timeToInternal(Time time) Converts the Java type used for UDF parameters of SQL TIME type (Time) to internal representation (int).
static long	timeToTimestampWithLocalZone(int time, TimeZone tz)
static int	toDate(int v)
static long	toTimestamp(DecimalData v)
static long	toTimestamp(double v)
static long	toTimestamp(long v)
static Long	toTimestamp(String dateStr)
static Long	toTimestamp(String dateStr, String format)
static Long	toTimestamp(String dateStr, String format, TimeZone tz) Parse date time string to timestamp based on the given time zone and format.
static Long	toTimestamp(String dateStr, TimeZone tz) Parse date time string to timestamp based on the given time zone and "yyyy-MM-dd HH:mm:ss" format.
static TimestampData	toTimestampData(String dateStr)
static TimestampData	toTimestampData(String dateStr, String format)
static Long	toTimestampTz(String dateStr, String tzStr)
static Long	toTimestampTz(String dateStr, String format, String tzStr) Parse date time string to timestamp based on the given time zone string and format.
static TimestampData	truncate(TimestampData ts, int precision)
static long	unixDateCeil(org.apache.calcite.avatica.util.TimeUnitRange range, long date)
static java.time.LocalDate	unixDateToLocalDate(int date)
static long	unixTimeExtract(org.apache.calcite.avatica.util.TimeUnitRange range, int ts)
static long	unixTimestamp() Returns a Unix timestamp in seconds since '1970-01-01 00:00:00' UTC as an unsigned integer.
static long	unixTimestamp(long ts) Returns the value of the timestamp to seconds since '1970-01-01 00:00:00' UTC.
static long	unixTimestamp(String dateStr)
static long	unixTimestamp(String dateStr, String format)
static long	unixTimestamp(String dateStr, String format, TimeZone tz) Returns the value of the argument as an unsigned integer in seconds since '1970-01-01 00:00:00' UTC.
static long	unixTimestamp(String dateStr, TimeZone tz) Returns the value of the argument as an unsigned integer in seconds since '1970-01-01 00:00:00' UTC.
static java.time.LocalDateTime	unixTimestampToLocalDateTime(long timestamp)
static java.time.LocalTime	unixTimeToLocalTime(int time)
static String	unixTimeToString(int time) Helper for CAST({time} AS VARCHAR(n)).

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

UTC_ZONE

public static final TimeZone UTC_ZONE

The UTC time zone.

Constructor Detail

SqlDateTimeUtils

public SqlDateTimeUtils()

Method Detail

internalToDate

public static Date internalToDate(int v)

Converts the internal representation of a SQL DATE (int) to the Java type used for UDF parameters (Date).

internalToTime

public static Time internalToTime(int v)

Converts the internal representation of a SQL TIME (int) to the Java type used for UDF parameters (Time).

internalToTimestamp

public static Timestamp internalToTimestamp(long v)

Converts the internal representation of a SQL TIMESTAMP (long) to the Java type used for UDF parameters (Timestamp).

dateToInternal

public static int dateToInternal(Date date)

Converts the Java type used for UDF parameters of SQL DATE type (Date) to internal representation (int).

Converse of internalToDate(int).

timeToInternal

public static int timeToInternal(Time time)

Converts the Java type used for UDF parameters of SQL TIME type (Time) to internal representation (int).

Converse of internalToTime(int).

timestampToInternal

public static long timestampToInternal(Timestamp ts)

Converts the Java type used for UDF parameters of SQL TIMESTAMP type (Timestamp) to internal representation (long).

Converse of internalToTimestamp(long).

toDate

public static int toDate(int v)

toTimestamp

public static long toTimestamp(long v)

toTimestamp

public static long toTimestamp(double v)

toTimestamp

public static long toTimestamp(DecimalData v)

toTimestampData

public static TimestampData toTimestampData(String dateStr)

toTimestampData

public static TimestampData toTimestampData(String dateStr,
                                            String format)

toTimestamp

public static Long toTimestamp(String dateStr)

toTimestamp

public static Long toTimestamp(String dateStr,
                               TimeZone tz)

Parse date time string to timestamp based on the given time zone and "yyyy-MM-dd HH:mm:ss" format. Returns null if parsing failed.

Parameters:

dateStr - the date time string

tz - the time zone

toTimestamp

public static Long toTimestamp(String dateStr,
                               String format,
                               TimeZone tz)

Parse date time string to timestamp based on the given time zone and format. Returns null if parsing failed.

Parameters:

dateStr - the date time string

format - date time string format

tz - the time zone

toTimestamp

public static Long toTimestamp(String dateStr,
                               String format)

toTimestampTz

public static Long toTimestampTz(String dateStr,
                                 String format,
                                 String tzStr)

Parse date time string to timestamp based on the given time zone string and format. Returns null if parsing failed.

Parameters:

dateStr - the date time string

format - the date time string format

tzStr - the time zone id string

toTimestampTz

public static Long toTimestampTz(String dateStr,
                                 String tzStr)

strToDate

public static int strToDate(String dateStr,
                            String fromFormat)

Returns the epoch days since 1970-01-01.

dateFormat

public static String dateFormat(TimestampData ts,
                                String format,
                                java.time.ZoneId zoneId)

Format a timestamp as specific.

Parameters:

ts - the TimestampData to format.

format - the string formatter.

zoneId - the ZoneId.

dateFormat

public static String dateFormat(TimestampData ts,
                                String format)

dateFormat

public static String dateFormat(TimestampData ts,
                                String format,
                                TimeZone zone)

dateFormat

public static String dateFormat(long ts,
                                String format,
                                TimeZone tz)

Format a timestamp as specific.

Parameters:

ts - the timestamp to format.

format - the string formatter.

tz - the time zone

dateFormat

public static String dateFormat(String dateStr,
                                String fromFormat,
                                String toFormat,
                                TimeZone tz)

Format a string datetime as specific.

Parameters:

dateStr - the string datetime.

fromFormat - the original date format.

toFormat - the target date format.

tz - the time zone.

dateFormat

public static String dateFormat(String dateStr,
                                String toFormat,
                                TimeZone tz)

dateFormat

public static String dateFormat(long ts,
                                String format)

dateFormat

public static String dateFormat(String dateStr,
                                String fromFormat,
                                String toFormat)

dateFormat

public static String dateFormat(String dateStr,
                                String toFormat)

dateFormatTz

public static String dateFormatTz(long ts,
                                  String format,
                                  String tzStr)

dateFormatTz

public static String dateFormatTz(long ts,
                                  String tzStr)

convertTz

public static String convertTz(String dateStr,
                               String format,
                               String tzFrom,
                               String tzTo)

Convert datetime string from a time zone to another time zone.

Parameters:

dateStr - the date time string

format - the date time format

tzFrom - the original time zone

tzTo - the target time zone

convertTz

public static String convertTz(String dateStr,
                               String tzFrom,
                               String tzTo)

timestampToString

public static String timestampToString(long ts,
                                       int precision)

timestampToString

public static String timestampToString(long ts,
                                       int precision,
                                       TimeZone tz)

Convert a timestamp to string.

Parameters:

ts - the timestamp to convert.

precision - the milli second precision to preserve

tz - the time zone

unixTimeToString

public static String unixTimeToString(int time)

Helper for CAST({time} AS VARCHAR(n)).

extractYearMonth

public static int extractYearMonth(org.apache.calcite.avatica.util.TimeUnitRange range,
                                   int v)

unixTimeExtract

public static long unixTimeExtract(org.apache.calcite.avatica.util.TimeUnitRange range,
                                   int ts)

extractFromTimestamp

public static long extractFromTimestamp(org.apache.calcite.avatica.util.TimeUnitRange range,
                                        TimestampData ts,
                                        TimeZone tz)

timestampFloor

public static long timestampFloor(org.apache.calcite.avatica.util.TimeUnitRange range,
                                  long ts)

timestampFloor

public static long timestampFloor(org.apache.calcite.avatica.util.TimeUnitRange range,
                                  long ts,
                                  TimeZone tz)

timestampCeil

public static long timestampCeil(org.apache.calcite.avatica.util.TimeUnitRange range,
                                 long ts)

timestampCeil

public static long timestampCeil(org.apache.calcite.avatica.util.TimeUnitRange range,
                                 long ts,
                                 TimeZone tz)

Keep the algorithm consistent with Calcite DateTimeUtils.julianDateFloor, but here we take time zone into account.

dateDiff

public static int dateDiff(long t1,
                           long t2,
                           TimeZone tz)

NOTE: (1). JDK relies on the operating system clock for time. Each operating system has its own method of handling date changes such as leap seconds(e.g. OS will slow down the clock to accommodate for this). (2). DST(Daylight Saving Time) is a legal issue, governments changed it over time. Some days are NOT exactly 24 hours long, it could be 23/25 hours long on the first or last day of daylight saving time. JDK can handle DST correctly. TODO: carefully written algorithm can improve the performance

dateDiff

public static int dateDiff(String t1Str,
                           long t2,
                           TimeZone tz)

dateDiff

public static int dateDiff(long t1,
                           String t2Str,
                           TimeZone tz)

dateDiff

public static int dateDiff(String t1Str,
                           String t2Str,
                           TimeZone tz)

dateDiff

public static int dateDiff(long t1,
                           long t2)

dateDiff

public static int dateDiff(String t1Str,
                           long t2)

dateDiff

public static int dateDiff(long t1,
                           String t2Str)

dateDiff

public static int dateDiff(String t1Str,
                           String t2Str)

dateSub

public static String dateSub(String dateStr,
                             int days,
                             TimeZone tz)

Do subtraction on date string.

Parameters:

dateStr - formatted date string.

days - days count you want to subtract.

tz - time zone of the date time string

Returns:

datetime string.

dateSub

public static String dateSub(long ts,
                             int days,
                             TimeZone tz)

Do subtraction on date string.

Parameters:

ts - the timestamp.

days - days count you want to subtract.

tz - time zone of the date time string

Returns:

datetime string.

dateSub

public static String dateSub(String dateStr,
                             int days)

dateSub

public static String dateSub(long ts,
                             int days)

dateAdd

public static String dateAdd(String dateStr,
                             int days,
                             TimeZone tz)

Do addition on date string.

Parameters:

dateStr - formatted date string.

days - days count you want to add.

Returns:

datetime string.

dateAdd

public static String dateAdd(long ts,
                             int days,
                             TimeZone tz)

Do addition on timestamp.

Parameters:

ts - the timestamp.

days - days count you want to add.

Returns:

datetime string.

dateAdd

public static String dateAdd(String dateStr,
                             int days)

dateAdd

public static String dateAdd(long ts,
                             int days)

fromTimestamp

public static long fromTimestamp(long ts)

fromUnixtime

public static String fromUnixtime(long unixtime,
                                  TimeZone tz)

Convert unix timestamp (seconds since '1970-01-01 00:00:00' UTC) to datetime string in the "yyyy-MM-dd HH:mm:ss" format.

fromUnixtime

public static String fromUnixtime(long unixtime,
                                  String format,
                                  TimeZone tz)

Convert unix timestamp (seconds since '1970-01-01 00:00:00' UTC) to datetime string in the given format.

fromUnixtime

public static String fromUnixtime(double unixtime,
                                  TimeZone tz)

fromUnixtime

public static String fromUnixtime(DecimalData unixtime,
                                  TimeZone tz)

fromUnixtime

public static String fromUnixtime(long unixtime)

fromUnixtime

public static String fromUnixtime(long unixtime,
                                  String format)

fromUnixtime

public static String fromUnixtime(double unixtime)

fromUnixtime

public static String fromUnixtime(DecimalData unixtime)

unixTimestamp

public static long unixTimestamp()

Returns a Unix timestamp in seconds since '1970-01-01 00:00:00' UTC as an unsigned integer.

unixTimestamp

public static long unixTimestamp(String dateStr,
                                 TimeZone tz)

Returns the value of the argument as an unsigned integer in seconds since '1970-01-01 00:00:00' UTC.

unixTimestamp

public static long unixTimestamp(String dateStr,
                                 String format,
                                 TimeZone tz)

Returns the value of the argument as an unsigned integer in seconds since '1970-01-01 00:00:00' UTC.

unixTimestamp

public static long unixTimestamp(String dateStr)

unixTimestamp

public static long unixTimestamp(String dateStr,
                                 String format)

unixTimestamp

public static long unixTimestamp(long ts)

Returns the value of the timestamp to seconds since '1970-01-01 00:00:00' UTC.

unixDateToLocalDate

public static java.time.LocalDate unixDateToLocalDate(int date)

localDateToUnixDate

public static int localDateToUnixDate(java.time.LocalDate date)

unixTimeToLocalTime

public static java.time.LocalTime unixTimeToLocalTime(int time)

localTimeToUnixDate

public static int localTimeToUnixDate(java.time.LocalTime time)

unixTimestampToLocalDateTime

public static java.time.LocalDateTime unixTimestampToLocalDateTime(long timestamp)

localDateTimeToUnixTimestamp

public static long localDateTimeToUnixTimestamp(java.time.LocalDateTime dateTime)

timestampToTimestampWithLocalZone

public static TimestampData timestampToTimestampWithLocalZone(TimestampData ts,
                                                              TimeZone tz)

timestampWithLocalZoneToTimestamp

public static TimestampData timestampWithLocalZoneToTimestamp(TimestampData ts,
                                                              TimeZone tz)

timestampWithLocalZoneToDate

public static int timestampWithLocalZoneToDate(long ts,
                                               TimeZone tz)

timestampWithLocalZoneToTime

public static int timestampWithLocalZoneToTime(long ts,
                                               TimeZone tz)

dateToTimestampWithLocalZone

public static long dateToTimestampWithLocalZone(int date,
                                                TimeZone tz)

timeToTimestampWithLocalZone

public static long timeToTimestampWithLocalZone(int time,
                                                TimeZone tz)

dateStringToUnixDate

public static Integer dateStringToUnixDate(String s)

timeStringToUnixDate

public static Integer timeStringToUnixDate(String v)

timeStringToUnixDate

public static Integer timeStringToUnixDate(String v,
                                           int start)

timestampToString

public static String timestampToString(TimestampData ts,
                                       int precision)

timestampToString

public static String timestampToString(TimestampData ts,
                                       TimeZone tz,
                                       int precision)

unixDateCeil

public static long unixDateCeil(org.apache.calcite.avatica.util.TimeUnitRange range,
                                long date)

truncate

public static TimestampData truncate(TimestampData ts,
                                     int precision)