#chen-sigma
tranquil flare
Hi koopajah! thanks for checking.
storm pollen
Okay so overall it works fine for me, it's just that you expect sigma to display the full / exact time like the hour/minute/second and it doesn't by default it just shows the day
so SELECT EXTRACT(HOUR FROM created), EXTRACT(HOUR FROM created AT TIME ZONE 'America/Los_Angeles') FROM subscriptions limit 3 should show you the difference with the hour localized, if that makes sense
and so you likely need to format the timestamp to your liking
storm pollen
sure thing!
tranquil flare
Is there a way to extract the date? Or I need to plus/minus the date based on the hour extracted?
storm pollen
not entirely sure what you mean
the column is a timestamp, so you can use date format methods to display the info you want
tranquil flare
thanks for the suggestion. Ultimately I want to display the date in PST. The line I'm writing now is date(created) - interval extract(hour from created AT TIME ZONE 'America/Los_Angeles') hour,, which is giving me syntax error.
storm pollen
@tranquil flare what syntax error do you get? Sorry we don't really write Sigma queries here but it's just normal SQL and I don't grasp what you are trying to do and why you remove an interval extract, like I don't grasp what the end goal is
tranquil flare
the end goal is to display subscriptions' creation dates in PST. right now some dates are one day off because of the time zone difference.
storm pollen
Okay, so have you tried formatting the date to show exact yyyy-mm-dd hh:mm:ss first?
tranquil flare
It seems the default output of created is e.g. 2021-09-16 17:23 +0000. I think having date, hour and minutes would be enough to know which day in PST it is.
ok I think this worked: date(at_timezone(created, 'America/Los_Angeles')),
storm pollen
ah awesome glad you figured it out