Question 119955 in Firebase Question posted in
An extensive official documentation can be found here.

Convert firebase timestamp to snowflake timestamp

I have a raw table from firebase in snowflake. All the information that I need is in the field "Data" (JSON). Because of that, I created a view to extract all the data in deferents columns.
I use json_extract_path_text(data, ‘createdAt’) to extract the createdAt timestamp. But, that function converts my data in a string like: {"_nanoseconds":248000000,"_seconds":1641850613}.

I want to convert them to TIMESTAMP format in snowflake. Is there anyway to do it?
I try with TO_TIMESTAMP but doesn’t work.

Tags:

2

Answers


  1. 0

    TO_TIMESTAMP(integer) could be used:

    If the format of the input parameter is a string that contains an integer:

    After the string is converted to an integer, the integer is treated as a number of seconds, milliseconds, microseconds, or nanoseconds after the start of the Unix epoch (1970-01-01 00:00:00.000000000 UTC).

    If the integer is less than 31536000000 (the number of milliseconds in a year), then the value is treated as a number of seconds.

    If the value is greater than or equal to 31536000000 and less than 31536000000000, then the value is treated as milliseconds.

    If the value is greater than or equal to 31536000000000 and less than 31536000000000000, then the value is treated as microseconds.

    If the value is greater than or equal to 31536000000000000, then the value is treated as nanoseconds.

    SELECT TO_TIMESTAMP('1644007937');

    Output:

    enter image description here

    Login or Signup to reply.
  2. 0

    You can use the TO_TIMESTAMP function as shown below but bear in mind that this works for seconds, as by default the timestamps in Snowflake will be treated as seconds (Deprecation Warning: Future versions of Snowflake might automatically interpret stringified integer values as seconds, rather than as milliseconds, microseconds, or nanoseconds.). For nanoseconds, you would need to divide it first by 10e9:

    select  to_timestamp(json_extract_path_text('{"_nanoseconds":248000000,"_seconds":1641850613}', '_seconds'));--2022-01-10 21:36:53.000

select to_timestamp(json_extract_path_text('{"_nanoseconds":248000000,"_seconds":1641850613}', '_nanoseconds')/1000000000);--1970-01-01 00:00:00.248
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search