Is there a shorter way to extract hour from timestamp stored as integer than EXTRACT(HOUR FROM TIMESTAMP_SECONDS(visitStartTime))?

Question

As in topic. While using standard-sql is there a shorter way to extract hour (or other dateparts) from timestamp stored as integer than EXTRACT(HOUR FROM TIMESTAMP_SECONDS(visitStartTime))?

Answer 1

You can use User-Defined Functions in such cases when you feel something should have shorter way to be expressed

See example below

CREATE TEMPORARY FUNCTION HOUR(time INT64)
RETURNS INT64 AS (
  EXTRACT(HOUR FROM TIMESTAMP_SECONDS(time))
);

SELECT 
  HOUR(visitStartTime) as `shorertWay`, 
  EXTRACT(HOUR FROM TIMESTAMP_SECONDS(visitStartTime)) as `longerWay`
FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`
LIMIT 10

So, now instead of

EXTRACT(HOUR FROM TIMESTAMP_SECONDS(visitStartTime)) 

you can use

HOUR(visitStartTime)