CODEWITHSUNDEEP
sqlgoogle-bigquery
Muhammad Hammad Ejaz
Muhammad Hammad Ejaz

Reputation: 51

How to get date of yesterday with the time part 00:00:00 in big query?

In big query how do I get the yesterday date with the time part 00:00:00?

Upvotes: 0

Views: 3778

Answers (4)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

Below is yet another option for BigQuery standard SQL 

#standardSQL
SELECT 
  TIMESTAMP_SECONDS(86400 * (UNIX_DATE(CURRENT_DATE()) - 1)) yesterday_as_timestamp,
  DATETIME(TIMESTAMP_SECONDS(86400 * (UNIX_DATE(CURRENT_DATE()) - 1))) yesterday_as_datetime

with result (as of answer day - April 02, 2019) 

Row     yesterday_as_timestamp      yesterday_as_datetime    
1       2019-04-01 00:00:00 UTC     2019-04-01T00:00:00

Note: 

DATETIME    Represents a year, month, day, hour, minute, second, and subsecond. Range: 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999.   
TIMESTAMP   Represents an absolute point in time, with microsecond precision.   Range: 0001-01-01 00:00:00 to 9999-12-31 23:59:59.999999 UTC.

A timestamp represents an absolute point in time, independent of any time zone or convention such as Daylight Savings Time.

TIMESTAMP provides microsecond precision.

Unlike Timestamps, a DATETIME object does not refer to an absolute instance in time. Instead, it is the civil time, or the time that a user would see on a watch or calendar.

You can see more details about DATETIME and TIMESTAMP in Data Types doc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

If you want a time part, then you want either a TIMESTAMP or DATETIME column.

So, either:

SELECT DATETIME(DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) as yesterday_dt,
       TIMESTAMP(DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY)) as yesterday_ts

Upvotes: 1

Tanzeel
Tanzeel

Reputation: 33

This code gets you date

SELECT DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE()));

For different methods check this link

MS SQL Server - How to get Date only from the datetime value?

Upvotes: 0

Mohammed Sherif KK
Mohammed Sherif KK

Reputation: 666

DATE Doesn't have time part if you want yesterday DateTime with 00:00 as time use:

SELECT DATETIME_TRUNC(DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 1 DAY), DAY) as yesterday;

if you want yesterday date use:

SELECT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) as yesterday;

Upvotes: 1

Related Questions