Aman Satya
05/13/2025, 10:47 AMvalid_from, contains timestamp values in the format:
2025-04-30T09:06:10.597000Z.
To derive additional time-based columns (day, week, month, and date) from this timestamp, I configured a set of transformation functions in the table config file. Here's what I’ve done:
"transformConfigs": [
{
"columnName": "valid_from_millis",
"transformFunction": "FromDateTime(valid_from, 'yyyy-MM-dd''T''HHmmss')"
},
{
"columnName": "valid_until_millis",
"transformFunction": "FromDateTime(valid_until, 'yyyy-MM-dd''T''HHmmss')"
},
{
"columnName": "valid_from_day",
"transformFunction": "dateTrunc('day', valid_from_millis)"
},
{
"columnName": "valid_from_week",
"transformFunction": "dateTrunc('week', valid_from_millis)"
},
{
"columnName": "valid_from_month",
"transformFunction": "dateTrunc('month', valid_from_millis)"
},
{
"columnName": "valid_from_date",
"transformFunction": "dateTimeConvert(valid_from_millis, '1MILLISECONDSEPOCH', '1DAYSSIMPLE_DATE_FORMAT:yyyy-MM-dd', '1:DAYS')"
}
]
My Approach:
1. Convert the ISO 8601 timestamp (valid_from) into milliseconds using FromDateTime.
2. Apply dateTrunc functions on the millisecond value to extract the day, week, and month.
3. Use dateTimeConvert to derive a yyyy-MM-dd formatted date string.
Problem:
Despite this setup, I’m seeing incorrect values in the table for these derived columns.
Below is screenshot of sample values that i am getting in tableGeorgi Varbanov
05/13/2025, 3:09 PMXiang Fu
convertTimestampToMillis(valid_from) to convert the String format valid_from to millis epoch long value valid_from_millisXiang Fu
fromEpochDays(toEpochDays(valid_from_millis)) to round the valueAman Satya
05/14/2025, 2:47 AMAman Satya
05/14/2025, 6:32 AMXiang Fu
Georgi Varbanov
05/14/2025, 7:27 AMGeorgi Varbanov
05/14/2025, 7:27 AMXiang Fu
public static void main(String[] args) {
String tsStr = "2022-09-27 18:05:32.222";
String pattern = "yyyy-MM-dd HH:mm:ss.SSS";
long ts = fromDateTime(tsStr, pattern);
System.out.println("ts: " + ts);
System.out.println("fromDateTime: " + fromDateTime(tsStr, pattern));
System.out.println("fromEpochSeconds(toEpochSeconds(fromDateTime(tsStr, pattern))) = " + fromEpochSeconds(toEpochSeconds(fromDateTime(tsStr, pattern))));
System.out.println("fromEpochDays(toEpochDays(fromDateTime(tsStr, pattern))) = " + fromEpochDays(toEpochDays(fromDateTime(tsStr, pattern))));
}Xiang Fu
ts: 1664301932222
fromDateTime: 1664301932222
fromEpochDays(toEpochDays(fromDateTime(tsStr, pattern))) = 1664236800000Xiang Fu
fromDateTime(valid_from, 'yyyy-MM-dd HH:mm:ss.SSS')Xiang Fu
Georgi Varbanov
05/14/2025, 7:50 AMGeorgi Varbanov
05/14/2025, 7:51 AMXiang Fu
Georgi Varbanov
05/14/2025, 8:39 AMGeorgi Varbanov
05/14/2025, 8:39 AMAman Satya
05/14/2025, 8:43 AMGeorgi Varbanov
05/14/2025, 8:44 AMGeorgi Varbanov
05/14/2025, 8:44 AMAman Satya
05/14/2025, 9:25 AMGeorgi Varbanov
05/14/2025, 9:31 AMGeorgi Varbanov
05/14/2025, 9:32 AMXiang Fu
Timestamp type is stored as LONG millis epoch value internallyGeorgi Varbanov
05/14/2025, 9:35 AM