implement to_unixtime() function

[waynexia]

What problem does the new feature solve?

Currently, we display all timestamp type in RFC3339 format, which is human-readable and widely used. However, sometimes we may need to convert them to the unix timestamp format, which is a number of seconds since January 1st 1970. This can be useful for debugging purposes, such as comparing timestamps across different sources or systems.

What does the feature do?

The feature will add a new UDF called to_unixtime(), which takes a timestamp in RFC3339 format as an input and returns its equivalent unix timestamp as an output. For example, to_unixtime("2023-03-01T06:35:02Z") will return 1678269302. The UDF will also handle different time zones and leap seconds correctly.

And when applied to a column with TIMESTAMP as type, to_unixtime will change how it's displayed.

We have implemented from_unixtime() which can be referenced. It does the opposite conversion.

Implementation challenges

No response

[etolbakov]

Hi @waynexia
I wonder if this is still relevant?
I found this TODO at the top of from_unixtime(), have a feeling that it would be true for to_unixtime as well.

[waynexia]

Hi @etolbakov, thanks for your interest!

That TODO is doable now. But DataFusion doesn't have to_unixtime() at present (just filed an issue apache/datafusion#5568). For this issue, I think it does need some updates:

• resolve that "TODO", replace our from_unixtime() with DataFusion's
• support to_unixtime() after Implement to_unixtime function apache/datafusion#5568 is done (you can also try Implement to_unixtime function apache/datafusion#5568!)

Are you willing to take the first task?

[etolbakov]

@waynexia yeah, I can give it go if it's not super urgent.
As for the to_unixtime() can we use to_timestamp_seconds for our purposes or am I missing something?

[waynexia]

As for the to_unixtime() can we use to_timestamp_seconds for our purposes or am I missing something?

to_timestamp_seconds is something more like from_unixtime(), it convert a integer to timestamp format. An example:

    "SELECT to_timestamp_seconds(ts) FROM ts_data LIMIT 3"

        "+--------------------------------+",
        "| totimestampseconds(ts_data.ts) |",
        "+--------------------------------+",
        "| 2020-09-08T13:42:29            |",
        "| 2020-09-08T12:42:29            |",
        "| 2020-09-08T11:42:29            |",
        "+--------------------------------+",

[yaaawww]

@waynexia Is this true?
#1103 (comment)
to_unixtime("2023-03-01T06:35:02Z") return 1677652502

mysql> select from_unixtime(1678269302); 
+---------------------------------+
| fromunixtime(Int64(1678269302)) |
+---------------------------------+
| 2023-03-08 09:55:02             |
+---------------------------------+
1 row in set (0.00 sec)

[yaaawww]

@waynexia I want to know how to add a UDF in sql.

[waynexia]

to_unixtime("2023-03-01T06:35:02Z") return 1677652502

That's it 👍

I want to know how to add a UDF in SQL.

You can take from_unixtime() as reference.

[etolbakov]

@evenyag @waynexia
this issue could be closed as the PRs have been merged, wdyt?

[waynexia]

@evenyag @waynexia this issue could be closed as the PRs have been merged, wdyt?

Close as completed. Thanks again @etolbakov

[killme2008]

Looks like the to_unxtime only supports string type right now, but we store the timestamps in timestamp type, we can't use this function to cast them into unix times:

create table test(a int, b timestamp time index);

insert into test values(1, 2);

And try to use to_unixtime:

select to_unixtime(b) from test;

Report error:

Internal error occurred during query exec, server actively close the channel to let client try next time: Failed to collect recordbatch, source: Failed to poll stream, source: External error: Failed to poll stream, source: Arrow error: Invalid argument error: column types must match schema types, expected Timestamp(Second, None) but found Int64 at column index 0.

I think this function should accept timestamp types as arguments too, we can change the signature into:

    fn signature(&self) -> Signature {
        Signature::uniform(1, 
            vec![ConcreteDataType::String(StringType), ConcreteDataType::Timestamp(TimestampType)],
            Volatility::Immutable,
        )
    }

[etolbakov]

@killme2008 I'm happy to add a fix here if it's not super urgent

[killme2008]

@killme2008 I'm happy to add a fix here if it's not super urgent

Cool, it's not urgent. Appreciate it if you can fix it. Thank you.