CODEWITHSUNDEEP
sql
developer1
developer1

Reputation: 93

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

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

Upvotes: 8

Views: 42626

Answers (7)

VolkanCetinkaya
VolkanCetinkaya

Reputation: 699

DATETIME to BIGINT;

SELECT CONVERT(BIGINT, FORMAT(CURRENT_TIMESTAMP, 'yyyyMMddHHmmss'))

OR

SELECT CONVERT(BIGINT, FORMAT(GETDATE(), 'yyyyMMddHHmmss'))

BIGINT to DATETIME;

SELECT CONVERT(DATETIME, FORMAT(20191220213340, '####-##-## ##:##:##'))

Upvotes: 0

Manuel
Manuel

Reputation: 31

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.

Upvotes: 1

user330315
user330315

Reputation:

For Postgres you can use:

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

Upvotes: 2

whati001
whati001

Reputation: 389

you can try something like that on MS SQL:

From bigint to datetime:

select dateadd(s, convert(bigint, '<bigint value like 1477958400000>') / 1000, convert(datetime, '1-1-1970 00:00:00'))

From datetime to bigint:

select cast(datediff(s, convert(datetime, '1-1-1970 00:00:00'), convert(datetime, '<datetime value like 11-01-2016>')) as bigint)* 1000

Cheers

Upvotes: 0

Kaf
Kaf

Reputation: 33809

Sql Server; Replace GETDATE() With your Column name

SELECT CONVERT(bigint,
       CONVERT(VARCHAR(12) , GETDATE() ,112) +  
       REPLACE(CONVERT(VARCHAR(8) , GETDATE() ,108),':',''))

Upvotes: -1

bendataclear
bendataclear

Reputation: 3850

The following will give you what (I think) you are after:

DECLARE @TM VARCHAR(50) = '2012-11-19 14:29:50.0'


SELECT (CAST(SUBSTRING(@TM,1,4) AS INT) * 10000000000)
     + (CAST(SUBSTRING(@TM,6,2) AS INT) * 100000000)
     + (CAST(SUBSTRING(@TM,9,2) AS INT) * 1000000)
     + (CAST(SUBSTRING(@TM,12,2) AS INT)* 10000)
     + (CAST(SUBSTRING(@TM,15,2) AS INT)* 100)
     + (CAST(SUBSTRING(@TM,18,2) AS INT))

However there may be a better solution to the overall problem which others have mentioned, if you give some more details we might be able to suggest a less dirty alternative.

Upvotes: 0

Yogendra Singh
Yogendra Singh

Reputation: 34367

For Oracle: Use to_char and to_number functions as below

    TO_NUMBER(TO_CHAR('2012-11-19 14:29:50.0', 'YYYYMMDDHHMISS'))

For MySQL: Use DATE_FORMAT & CONVERT as below:

  CONVERT(DATE_FORMAT('2012-11-19 14:29:50.0'', '%Y%M%d%H%i%s'), UNSIGNED BIGINT)

Upvotes: 0

Related Questions