Convert Hexadecimal to date formating utc-5 Postgresql

Question

Hello friends I am trying to convert a hexadecimal to date that must be in utc-5 format peru time

this is the function with which I try to do this conversion:

CREATE OR REPLACE FUNCTION public.hex_timestamp(
    text)
    RETURNS timestamp without time zone
    LANGUAGE 'sql'

    COST 100
    IMMUTABLE STRICT 
    
AS $BODY$
select 
    (to_timestamp(('x' || lpad($1, 16, '0'))::bit(64)::bigint / 1000.0))::timestamptz at time zone 'utc-5'
$BODY$;

ALTER FUNCTION public.hex_timestamp(text)
    OWNER TO postgres;

and this is my hex value:

0000017B3DB65648

However, the time is not correct since it takes me one day ahead of the current date and the time is not correct either

13/08/2021 04:12:45

I would like you to show me the correct date and time with Peruvian time, this is the correct time that it should show

12/08/2021 23:23:53

thanks for your help

Answer 1

You seem to be using my hex-to-decimal conversion from this related answer:

• Convert hex in text representation to decimal number

This converts hex 0000017B3DB65648 to decimal 1628827965000:

SELECT ('x' || lpad('0000017B3DB65648', 16, '0'))::bit(64)::bigint;  -- 1628827965000

That number seems to represent the number of milliseconds since the Postgres epoch (2000-01-01 00:00:00). See:

• Ignoring time zones altogether in Rails and PostgreSQL

SELECT to_timestamp(1628827965000 / 1000) AT TIME ZONE 'UTC';         -- '2021-08-13 06:12:45'
     , extract('epoch' FROM timestamp '2021-08-13 04:12:45') * 1000;  -- 1628827965000

Your desired result is 2021-08-13 23:23:53, that's exactly an interval of 19:11:08 later (19 hours, 11 minutes, 8 seconds).
That's 69068000 milliseconds, the difference between 1628897033000 and 1628827965000.

And that does not seem to make sense. We can't get to your desired result with adjustment for time zones.

Please check your question for typos and tell us what the hex number is supposed to represent exactly.