EXTRACT(EPOCH from column)

[mkmik]

Is your feature request related to a problem or challenge? Please describe what you are trying to do.

Datafusion supports a few date parts like "year" or "seconds" in the EXTRACT operator.

Postgres offers a few more. In particular it offers an EPOCH date part which extracts the number of seconds since 1970-01-01 00:00:00-00 (can be negative).

See https://www.postgresql.org/docs/8.1/functions-datetime.html

My particular use case is using pre-existing grafana postgres plugin, which crafts queries with such a construct. But I think it would be generally useful to support converting a timestamp to a unix epoch.

Describe the solution you'd like

select EXTRACT(EPOCH from column) from mytable;

Describe alternatives you've considered

select CAST(column AS bigint)/1000000000 from mytable;

[alamb]

Here is an example of how other parts were added: #1974

This may require adding epoch kernel to arrow, along the lines of apache/arrow-rs#1891 and then adding appropriate support

[mkmik]

I have a draft in #2786 ; I tried it out with datafusion-cli

Next, I'll:

1. add tests
2. try to see if I can simplify it and make it use the same macros that we use for the other date field extractors

[alamb]

BTW here is a nice contained test case:

❯ select extract(epoch from '1825-08-29T07:29:01.256'::timestamp);
ArrowError(ExternalError(Execution("Date part 'EPOCH' not supported")))

I think this would be a good first issue

[toprakdeniz]

Hello Andrew, I need to, and want to help datafusion project. I think it is a good start for me. I am new to Rust language and this will be the first open source support for me. I am familiar with software engineering and I have read Andrew Grove's book. Best Regards, Toprak Deniz Andrew Lamb ***@***.***>, 12 Oca 2023 Per, 00:01 tarihinde şunu yazdı:

…

BTW here is a nice contained test case: ❯ select extract(epoch from '1825-08-29T07:29:01.256'::timestamp); ArrowError(ExternalError(Execution("Date part 'EPOCH' not supported"))) I think this would be a good first issue — Reply to this email directly, view it on GitHub <#2785 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AKZX6ZCZX3DRUBRTDJGPHP3WR4NRLANCNFSM5ZYEUSKA> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[alamb]

Welcome to the community @toprakdeniz ! We have many first time Rust enthusiasts work on DataFusion and look forward to helping get this feature done.