Convert Hexadecimals to timestamp in postgres

Question

I have the following hexadecimal data that I want to convert using the postgres functions, the data they receive is the following:

   @AVLData_TimeStamp          @AVLData_GPSElement_Altitude
   00000174C0FA7EA0            0140
   

And I want to convert them to the following data

  @MOPO_FECHAHORA             @MOPO_ALTITUD
  2020-09-24 09:37:56.000     320

I have tried to do it using this function but have not succeeded

  SELECT to_timestamp(@AVLData_TimeStamp)

I have two functions in SQLserver that do this but in postgres I don't know how to do it

First Function:

CREATE FUNCTION FUN_Unix_To_Date(
    @FechaUnix CHAR(38)
)
RETURNS DATETIME
AS
BEGIN
    DECLARE
     @LocalTime BIGINT
    ,@Adjusted  BIGINT
    ,@seconds   BIGINT = @FechaUnix

    SET @LocalTime = DATEDIFF(MS,GETDATE(),GETUTCDATE())
    SET @Adjusted = @seconds - @LocalTime

    RETURN (DATEADD(SECOND,CONVERT(BIGINT, LEFT(CAST(@Adjusted AS VARCHAR(100)),13))/1000, CAST('1970-01-01 00:00:00' AS DATETIME)))

END

Two Function:

CREATE FUNCTION HexadecimalToDec_v2(
    @hexval CHAR(38)
)
RETURNS NUMERIC(38)
AS
BEGIN
    DECLARE  @i INT
            ,@digits INT
            ,@result NUMERIC
            ,@current_digit CHAR(1)
            ,@current_digit_dec NUMERIC
    SET @digits = LEN(@hexval)
    SET @i = 0
    SET @result =0

    WHILE @i <= @digits  
    BEGIN
        SET @current_digit = SUBSTRING(@hexval, @i, 1)
        
        IF  @current_digit = 'A' OR 
            @current_digit = 'B' OR
            @current_digit = 'C' OR
            @current_digit = 'D' OR
            @current_digit = 'E' OR
            @current_digit = 'F' 

            SET @current_digit_dec = ASCII(@current_digit) - ASCII('A') + 10

        ELSE
            SET @current_digit_dec = CONVERT(INT,@current_digit)

            SET @result = (@result * 16) + @current_digit_dec

        SET @i = @i + 1
    END
RETURN(@result)
END

and finally the function is used like this

@MOPO_FECHAHORA = (dbo.Unix_To_Date(dbo.FUN_HexadecimalToDec_v2(@AVLData_TimeStamp)))

And this is the result

 @MOPO_FECHAHORA             
  2020-09-24 09:37:56.000    

Thank you very much for your help.

Answer 1

Use the function from this answer to convert hexadecimal to decimal (you might want to use bigint rather than integer):

CREATE OR REPLACE FUNCTION hex_to_int(hexval text) RETURNS bigint
   LANGUAGE plpgsql IMMUTABLE STRICT AS
$$DECLARE
   result bigint;
BEGIN
   EXECUTE 'SELECT x' || quote_literal(hexval) || '::bigint'
   INTO result;
   RETURN result;
END;$$;

The timestamp can then be converted with

to_timestamp(hex_to_int('00000174C0FA7EA0') / 1000.0)

Answer 2

Just to clarify the source of issue.

The problem is that the your value(s) is the "local" epoch but to_timestamp() function returns timestamp with timezone value. Lets try some example:

with t(x) as (values('2020-09-24 09:37:56'))
  select
    x::timestamp as srcts,
    x::timestamptz as srctstz,
    to_timestamp(extract(epoch from x::timestamp)) as cnvts,
    to_timestamp(extract(epoch from x::timestamptz)) as cnvtstz
  from t;

┌─[ RECORD 1 ]─────────────────────┐
│ srcts   │ 2020-09-24 09:37:56    │
│ srctstz │ 2020-09-24 09:37:56+03 │
│ cnvts   │ 2020-09-24 12:37:56+03 │ <- Here is the issue in our case
│ cnvtstz │ 2020-09-24 09:37:56+03 │
└─────────┴────────────────────────┘

As you can see the data source type is critical. That's why you got the increased (actually converted from UTC to the local time zone) value in the first try using @LaurenzAlbe answer.

To fix this issue you need to perform some "reverse" calculations:

with t(x) as (values('2020-09-24 09:37:56'))
  select
    x::timestamp as srcts,
    x::timestamptz as srctstz,
    to_timestamp(extract(epoch from x::timestamp)) as cnvts,
    (to_timestamp(extract(epoch from x::timestamp)))::timestamptz at time zone 'utc' as cnvtsrecalc,
    to_timestamp(extract(epoch from x::timestamptz)) as cnvtstz
  from t;

┌─[ RECORD 1 ]┬────────────────────────┐
│ srcts       │ 2020-09-24 09:37:56    │
│ srctstz     │ 2020-09-24 09:37:56+03 │
│ cnvts       │ 2020-09-24 12:37:56+03 │ <- Here is the issue in our case
│ cnvtsrecalc │ 2020-09-24 09:37:56    │ <- Issue fixed
│ cnvtstz     │ 2020-09-24 09:37:56+03 │
└─────────────┴────────────────────────┘

The following function wraps all this logic including the conversion of the hex value to bigint:

create or replace function hex2ts(text)
  returns timestamp
  language sql
  immutable
  strict
as $$
  select 
    (to_timestamp(('x' || lpad($1, 16, '0'))::bit(64)::bigint / 1000.0))::timestamptz at time zone 'utc'
$$;