Convert excel date to timestamp in PostgreSQL

Question

I need to convert number to timestamp in PostgreSQL. I got numbers in database as character varying. I have seen there is a lot of solutions for integers but I got decimal numbers and here comes the issue.

The max I got is something like below using '1899-12-30'::DATE + CAST(Round(CAST(REPLACE(Excel_date_number, ',','.') as DOUBLE PRECISION)) as INTEGER)

Value	Date
45279,4029282407	2023-12-19
45294,5203472222	2024-01-04
45309,2083333333	2024-01-18

But I am completely lost in getting thw whole timestamp from these numbers. Can you give me any ideas how could I handle this?

Answer 1

Assuming you have the proper locale installed on your computer:

set lc_numeric = 'de_DE.utf8';

WITH ts_number AS (
    SELECT
        to_number('45279,4029282407', '99999D9999999999') AS value
)
SELECT
    ('1899-12-30'::date + ((trunc(value)::text || 'days')::interval))
+ (((value - trunc(value)) * 24)::text || 'hours')::interval
FROM
    ts_number;

 ?column?          
----------------------------
 2023-12-19 09:40:12.999996

If you where to go this route I would suggest putting the above logic in a function and use that. It would make your queries a lot cleaner. The simpler solution per my comment would be to export the formatted datetime string from Excel not the underlying value. Lastly the above does not take into account timezone and just assumes you are working in the same timezone as that of Excel.

Answer 2

As suggested, the case is generic enough to be worth a function. Here are my 5 cents, basically the same as above.

create or replace function xlts_to_ts(xlts text)
returns timestamp language sql immutable as 
$$
with t(v) as (select replace(xlts,',','.')::numeric)
select date '1899-12-30' + 
       v::integer * interval '1 day' +
       v % 1 * 24 * interval '1 hour'
from t
$$;

Please note that Windows regional settings affect the way that numbers are presented by Excel. The decimal symbol may be a dot or a comma. There may be even a "thousands separator" (bookkeepers love it) which too can be a comma or a dot. So - as @AdrianKlaver noted in his answer - whenever possible explicitly "export the formatted datetime string from Excel not the underlying value". The above function will work if no "thousands separator" is set.