Skip to content

bigfunctions > timestamp_to_unix_date_time

timestamp_to_unix_date_time

Call or Deploy timestamp_to_unix_date_time ?

✅ You can call this timestamp_to_unix_date_time bigfunction directly from your Google Cloud Project (no install required).

  • This timestamp_to_unix_date_time function is deployed in bigfunctions GCP project in 39 datasets for all of the 39 BigQuery regions. You need to use the dataset in the same region as your datasets (otherwise you may have a function not found error).
  • Function is public, so it can be called by anyone. Just copy / paste examples below in your BigQuery console. It just works!
  • You may prefer to deploy the BigFunction in your own project if you want 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). Discover the framework

Public BigFunctions Datasets:

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

Description

Signature

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

Examples

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         |
+------------+

Need help using timestamp_to_unix_date_time?

The community can help! Engage the conversation on Slack

For professional suppport, don't hesitate to chat with us.

Found a bug using 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.

For professional suppport, don't hesitate to chat with us.

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.

Spread the word

BigFunctions is fully open-source. Help make it a success by spreading the word!

Share on Add a on