PostgreSQL Question posted in
The official documentation can be found here.

Convert excel date to timestamp in PostgreSQL

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search