How to convert a timestamp (DATE FORMAT) to BIGINT in SQL

Question

I need to convert the timestamp value (Format: 2012-11-19 14:29:50.0) to a BIGINT value (Format: 2012111315041650 = YYYYMMDDhhmmss). I need to insert current time in a column of a table which accepts only BIGINT.

I am using Squirrel SQL Client Version 3.3.0. The query I am using right now is

INSERT INTO table1 (BINGINT_DATE, TIMESTAMP_DATE) 
VALUES (2012111315041650, CURRENT_TIMESTAMP); 

Instead of manually entering the BIGINT_DATE value, I want to convert the CURRENT_TIMESTAMP or NOW() to a BIGINT value as the format YYYYMMDDHHMISS

Something like

INSERT INTO table1 (BINGINT_DATE, TIMESTAMP_DATE) 
VALUES ("CONVERT(CURRENT_TIMESTAMP,BIGINT)", CURRENT_TIMESTAMP); 

Let me know if it is feasible to do this way

Please help me with this.

Thanks

Answer 1

For Postgres you can use:

INSERT INTO table1 (BINGINT_DATE, TIMESTAMP_DATE) 
VALUES (to_char(current_timestamp, 'yyyymmddhh24miss')::bigint, CURRENT_TIMESTAMP); 

Answer 2

To convert timestamp values to bigint values (in seconds) you can do it in this way:

SELECT (EXTRACT(EPOCH FROM TIMESTAMP '2019-04-02T14:56:39.009'))::bigint -- -> 1554216999

To convert timestamp values to bigint values (in millis) you can do it in this way:

SELECT ((EXTRACT(EPOCH FROM TIMESTAMP '2019-04-02T14:56:39.009'))::numeric * 1000)::bigint -- -> 1554216999009

If you want to convert all timestamp values of a column into a table into another column of the same table you can run this query (result in millis):

UPDATE $table_name$ SET $column_with_values_bigint$ = (EXTRACT(EPOCH FROM $column_with_values_timestamp$)::numeric * 1000)::bigint

Hope this help.