Having trouble marking columns as timestamps

faq
#1

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.

#2

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.