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 inbigfunctions
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
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
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!