postgresql Realization of Conversion of Hexadecimal String to Decimal Digit

I won't talk too much, let's just look at the code ~

CAST(CAST(( ' x' ||  ' 47868E22') AS bit(32)) AS int)

Additional: Interchange of timestamp and date in Postgresql (also applicable to GreenPlum)

-- Date to timestamp 
SELECT EXTRACT(epoch FROM NOW());
SELECT EXTRACT(epoch FROM CAST('2017-12-06 00:17:10' AS TIMESTAMP));
 
-- Time stamp to date 
SELECT TO_TIMESTAMP(1512490630)

Supplement: postgresql timestamp converted to normal time

Unix timestamp (Unix timestamp), or Unix time (Unix time) and POSIX time (POSIX time), is a time representation, which is defined as the total number of seconds from 00:00: 00 GMT on January 1, 1970 to the present.

Unix timestamps are widely used not only in Unix systems and Unix-like systems, but also in many other operating systems.

When postgresql queries data, in many cases, the time format is timestamp style, which is poor in readability and needs to be converted into ordinary format. When querying, the conversion method is as follows:

select md5, to_char(to_timestamp(time),'yyyy-MM-dd HH24-MI-SS') as time from tablename;