timestamp_to_unix_date_time¶

timestamp_to_unix_date_time(timestamp_expression, date_time_part)

Description¶

Returns the number of date_time_part since 1970-01-01 00:00:00 UTC. Truncates higher levels of precision by rounding down to the beginning of the date_time_part.

Param	Possible values
`date_time_part`	`MICROSECOND`, `MILLISECOND`, `SECOND`, `MINUTE`, `HOUR`, `DAY`, `WEEK`, `WEEK(MONDAY)`, `WEEK(TUESDAY)`, `WEEK(WEDNESDAY)`, `WEEK(THURSDAY)`, `WEEK(FRIDAY)`, `WEEK(SATURDAY)`, `WEEK(SUNDAY)`, `MONTH`, `QUARTER`, `YEAR`

Usage¶

Call or Deploy timestamp_to_unix_date_time ?

Call timestamp_to_unix_date_time directly

The easiest way to use bigfunctions

timestamp_to_unix_date_time function is deployed in 39 public datasets for all of the 39 BigQuery regions.
It can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
(You need to use the dataset in the same region as your datasets otherwise you may have a function not found error)

Public BigFunctions Datasets

Region	Dataset
`eu`	`bigfunctions.eu`
`us`	`bigfunctions.us`
`europe-west1`	`bigfunctions.europe_west1`
`asia-east1`	`bigfunctions.asia_east1`
...	...

Deploy timestamp_to_unix_date_time in your project

Why deploy?

You may prefer to deploy timestamp_to_unix_date_time in your own project to build and manage your own catalog of functions.
This is particularly useful if you want to create private functions (for example calling your internal APIs).
Get started by reading the framework page

Deployment

timestamp_to_unix_date_time function can be deployed with:

pip install bigfunctions
bigfun get timestamp_to_unix_date_time
bigfun deploy timestamp_to_unix_date_time

Examples¶

EUUSeurope-west1

select bigfunctions.eu.timestamp_to_unix_date_time(timestamp("2001-01-01"), "YEAR")

select bigfunctions.us.timestamp_to_unix_date_time(timestamp("2001-01-01"), "YEAR")

select bigfunctions.europe_west1.timestamp_to_unix_date_time(timestamp("2001-01-01"), "YEAR")

+------------+
| unix_value |
+------------+
| 31         |
+------------+

Use cases¶

Use Case 1: Event Time Difference Calculation

Imagine you have a table of events with timestamps, and you want to calculate the time elapsed between events in a specific unit (e.g., days, hours, minutes). timestamp_to_unix_date_time can help achieve this.

SELECT
    event_id,
    event_timestamp,
    bigfunctions.YOUR_REGION.timestamp_to_unix_date_time(event_timestamp, 'SECOND') -
        LAG(bigfunctions.YOUR_REGION.timestamp_to_unix_date_time(event_timestamp, 'SECOND')) OVER (PARTITION BY user_id ORDER BY event_timestamp) AS time_difference_seconds
FROM
    your_event_table

This query calculates the difference in seconds between consecutive events for each user. You can change 'SECOND' to 'MINUTE', 'HOUR', 'DAY', etc., depending on the desired unit.

Use Case 2: Bucketing Events by Time Intervals

You might want to group events into specific time intervals for analysis, such as hourly, daily, or weekly buckets. timestamp_to_unix_date_time allows you to generate bucket identifiers.

SELECT
    event_id,
    event_timestamp,
    bigfunctions.YOUR_REGION.timestamp_to_unix_date_time(event_timestamp, 'HOUR') AS hour_bucket
FROM
    your_event_table

This query assigns each event to an hourly bucket based on its timestamp. Events within the same hour will have the same hour_bucket value. You can then use this hour_bucket for aggregation or filtering.

Use Case 3: Data Retention Policies

For implementing data retention policies, you can use timestamp_to_unix_date_time to identify data older than a specific period.

SELECT
    *
FROM
    your_data_table
WHERE
    bigfunctions.YOUR_REGION.timestamp_to_unix_date_time(CURRENT_TIMESTAMP(), 'DAY') - bigfunctions.YOUR_REGION.timestamp_to_unix_date_time(data_timestamp, 'DAY') > 30  -- Delete data older than 30 days

This query selects data older than 30 days. You can modify the condition and integrate it into a DELETE statement to automatically remove old data.

Use Case 4: Simplified Date Arithmetic

Sometimes you need to perform date arithmetic but don't want to deal with complexities of date and timestamp functions. Converting to Unix time can simplify these calculations. For example, adding 7 days to a timestamp becomes as simple as adding 7 * 24 * 60 * 60 to the Unix timestamp representation.

Important Note: Remember to replace YOUR_REGION with the appropriate BigQuery region (e.g., us, eu, us-central1) where you are running your query.

Need help or Found a bug?

Get help using timestamp_to_unix_date_time

The community can help! Engage the conversation on Slack

We also provide professional suppport.

Report a bug about timestamp_to_unix_date_time

If the function does not work as expected, please

report a bug so that it can be improved.
or open the discussion with the community on Slack.

We also provide professional suppport.

Show your by adding a on