fix(cubesql): Support to_timestamp(epoch) pg function, fix #8901

[droidraja]

Check List

• [x] Tests has been run in packages where changes made if available
• [x] Linter has been run for changed code
• [x] Tests for the changes have been added if not covered yet
• [ ] Docs have been added / updated if required

Issue Reference this PR resolves

8901

[vercel[bot]]

The latest updates on your projects. Learn more about Vercel for Git ↗︎

8 Skipped Deployments

| Name | Status | Preview | Comments | Updated (UTC) | | :--- | :----- | :------ | :------- | :------ | | **examples-angular-dashboard** | ⬜️ Ignored ([Inspect](https://vercel.com/cube-dev/examples-angular-dashboard/EHVF8W7JHE1vyEMJ242RGhhUismd)) | [Visit Preview](https://examples-angular-dashboard-git-fork-droidraja-c-916338-cube-dev.vercel.app) | | Nov 14, 2024 4:42pm | | **examples-react-d3** | ⬜️ Ignored ([Inspect](https://vercel.com/cube-dev/examples-react-d3/2F1gNBPmuxu3JppdcPjNAJGrdAGv)) | [Visit Preview](https://examples-react-d3-git-fork-droidraja-cubesql-to-374b09-cube-dev.vercel.app) | | Nov 14, 2024 4:42pm | | **examples-react-dashboard** | ⬜️ Ignored ([Inspect](https://vercel.com/cube-dev/examples-react-dashboard/HKYrNys8gBKg7hvyTxzpSEV4Ee1t)) | [Visit Preview](https://examples-react-dashboard-git-fork-droidraja-cub-f3ab08-cube-dev.vercel.app) | | Nov 14, 2024 4:42pm | | **examples-react-data-table** | ⬜️ Ignored ([Inspect](https://vercel.com/cube-dev/examples-react-data-table/CyHKan2AiV1fSYTQUpyq5QqjwiSc)) | [Visit Preview](https://examples-react-data-table-git-fork-droidraja-cu-d269c9-cube-dev.vercel.app) | | Nov 14, 2024 4:42pm | | **examples-react-highcharts** | ⬜️ Ignored ([Inspect](https://vercel.com/cube-dev/examples-react-highcharts/6Kj2jQv6iyPk1imt4aq2q7ky6exg)) | [Visit Preview](https://examples-react-highcharts-git-fork-droidraja-cu-f2df91-cube-dev.vercel.app) | | Nov 14, 2024 4:42pm | | **examples-react-material-ui** | ⬜️ Ignored ([Inspect](https://vercel.com/cube-dev/examples-react-material-ui/2BhsTtv4jGqeLg5yjmTxy2dGnD1q)) | [Visit Preview](https://examples-react-material-ui-git-fork-droidraja-c-e108e6-cube-dev.vercel.app) | | Nov 14, 2024 4:42pm | | **examples-react-pivot-table** | ⬜️ Ignored ([Inspect](https://vercel.com/cube-dev/examples-react-pivot-table/B1F14PVFYQEghMAUugNvLgu76GYx)) | [Visit Preview](https://examples-react-pivot-table-git-fork-droidraja-c-4e1d65-cube-dev.vercel.app) | | Nov 14, 2024 4:42pm | | **examples-vue-query-builder** | ⬜️ Ignored ([Inspect](https://vercel.com/cube-dev/examples-vue-query-builder/AFd5z5wgbh7NtoBLy6ktcqBLFaFc)) | [Visit Preview](https://examples-vue-query-builder-git-fork-droidraja-c-c1d91c-cube-dev.vercel.app) | | Nov 14, 2024 4:42pm |

[paveltiunov]

@droidraja Thanks for contributing! Could you please provide at least one test for it? Thanks!

[droidraja]

@droidraja Thanks for contributing! Could you please provide at least one test for it? Thanks!

@paveltiunov, This does not work for complex queries like to_timetamp(cast(epoch_varchar as numeric(10))) which is being issued by quicksight, will add a UDF to handle the same and push the changes with tests in some time

[droidraja]

@paveltiunov have added the udf and testcases, please go ahead with the review.

[igorlukanin]

@droidraja Could you also please update the docs here? https://cube.dev/docs/reference/sql-api#data-type-formatting-functions

[droidraja]

@igorlukanin This PR and the existing functionality supports evaluation of constant expression to cube time dimension filter and to_timestamp can't be applied to any columns. So it doesn't support both projection and post processing that is mentioned in the docs.

@paveltiunov please correct my understanding if there are any holes.