Having trouble marking columns as timestamps

Jason Harris

• June 07, 2018 20:35

I’m having trouble getting data to group properly and get read by Metabase. We had some other tables where this was an issue. To solve that, we had created a view:

SELECT
"data".event,
("data".payload__event__created_at) :: timestamp without time zone AS payload__event__created_at
FROM
"public"."hubspot_primary_contact_properties_rk";

And that worked fine. But now, when I try to do something similar, I get an invalid data error. I’ve tried quite a few permutations of that sql query:

SELECT
("public"."hubspot_primary_contact_properties_rk".createdate) :: timestamp without time zone AS createdate
FROM
"public"."hubspot_primary_contact_properties_rk";

I’m not sure where things are going wrong. One is a table and the other is a view, but woudl that matter? My goal is to get the data recognized and grouped as a timestamp properly in Metabase.

0

• 

  Alon Brody

  • June 07, 2018 20:47

  I looked through the key:value rotation used to create the “public”.“hubspot_contact_properties_primary_v2_roman” view, and noticed the following:

  "max"( ( (CASE
  WHEN (
      (
        hubspot_contacts_properties."key"
      )
      :: text = ('createdate' :: character VARYING) :: text
    )
    THEN
    ( '1970-01-01 00:00:00' :: timestamp without time zone + ( ( ( (hubspot_contacts_properties.value) :: bigint / 1000 ) ) :: DOUBLE PRECISION * '00:00:01' :: interval ) )
    ELSE NULL :: timestamp without time zone
  END ) :: character VARYING ) :: text ) AS createdate,
  

  Looks like it was casting the column name as a text column. I changed that to timestamp without time zone and now it works as it should.

  0

Please sign in to leave a comment.