Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
What's the best way to convert a TIMESTAMP to a BIGINT In Azure Synapse. I tried this (which works) but seems clumsy. Is there a better, more concise and efficient way of doing the same thing?
SELECT CURRENT_TIMESTAMP AS CURR_TS,
CONVERT(BIGINT,
CONVERT(CHAR(8), CURRENT_TIMESTAMP, 112) +
REPLACE(CONVERT(CHAR(12), CURRENT_TIMESTAMP, 114), ':', '')) AS CURR_TS_NUM
Slightly more compact and only one call to CURRENT_TIMESTAMP, you can use CONVERT with its format switch (121) and TRANSLATE and REPLACE to tidy up. This reduces the statement from six individual function calls to four. You can also use FORMAT, which is only two function calls:
SELECT
CURRENT_TIMESTAMP ts,
REPLACE( TRANSLATE( CONVERT( CHAR(23), CURRENT_TIMESTAMP, 121 ), '-:.', ' ' ), ' ', '' ) c1,
FORMAT( CURRENT_TIMESTAMP , 'yyyyMMddHHmmssfff' ) c2
I would query the need to store these values however. You probably should be storing a conventional timestamp for event records so why also store this form of timestamp in an encoded fashion which you have to unpack if you want to use? Logging to a table with an identity column has a similar effect. Something to think about.
