Using standart sql, how to convert timestamp to unix time?

Question

In mysql, one can do this in order to compare 2 timestamp :

where UNIX_TIMESTAMP(transaction.closed_on)<UNIX_TIMESTAMP(transaction.opened_on)...

What’s the equivalent function for Google bigquery ?

Subtract? With the the less-than operator? – Caius Jard Oct 23 '19 at 19:59 — Caius Jard, Oct 23 '19 at 19:59
@CaiusJard I wanted to write compare sorry. – user2284570 Oct 23 '19 at 20:01 — user2284570, Oct 23 '19 at 20:01

score 0 · Accepted Answer · answered Oct 23 '19 at 19:56

... to subtract 2 timestamp : ...

you can use TIMESTAMP_DIFF(closed_on, opened_on, SECOND) for BigQuery Standard SQL

to compare in WHERE clause

WHERE closed_on < opened_on

finally, to convert timestamp to unix time ...

Use UNIX_SECONDS() function

score 0 · Answer 2 · answered Oct 23 '19 at 19:58

0

It is called UNIX_SECONDS:

select UNIX_SECONDS(TIMESTAMP("2008-12-25 15:30:00")) - UNIX_SECONDS(TIMESTAMP("2008-11-25 15:30:00"))

answered Oct 23 '19 at 19:58

Yun Zhang

2 Answers2