Labels

Apache Hadoop (3) ASP.NET (2) AWS S3 (2) Batch Script (3) BigQuery (21) BlobStorage (1) C# (3) Cloudera (1) Command (2) Data Model (3) Data Science (1) Django (1) Docker (1) ETL (7) Google Cloud (5) GPG (2) Hadoop (2) Hive (3) Luigi (1) MDX (21) Mongo (3) MYSQL (3) Pandas (1) Pentaho Data Integration (5) PentahoAdmin (13) Polybase (1) Postgres (1) PPS 2007 (2) Python (13) R Program (1) Redshift (3) SQL 2016 (2) SQL Error Fix (18) SQL Performance (1) SQL2012 (7) SQOOP (1) SSAS (20) SSH (1) SSIS (42) SSRS (17) T-SQL (75) Talend (3) Vagrant (1) Virtual Machine (2) WinSCP (1)

Tuesday, August 23, 2016

Google BigQuery: Calculating Time difference in Minute and Hours

Assume you have a date column "OrderedDate", then we can calculate the total difference in minute and hour with respect to current date time:

SELECT
(CURRENT_TIMESTAMP() - TIMESTAMP_TO_USEC(OrderedDate)) / 1000000/ 3600 AS Diff_Hour,
 (CURRENT_TIMESTAMP() - TIMESTAMP_TO_USEC(OrderedDate)) / 1000000/ 60 AS Diff_Minute

FROM [Project:Dataset.Table]