Skip to content

bigfunctions > timestamp_from_unix_date_time

timestamp_from_unix_date_time

Call or Deploy timestamp_from_unix_date_time ?

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

  • This timestamp_from_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_from_unix_date_time(unix_date_time, date_time_part)

Description

Interprets unix_date_time as 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_from_unix_date_time(31, "YEAR")
select bigfunctions.us.timestamp_from_unix_date_time(31, "YEAR")
select bigfunctions.europe_west1.timestamp_from_unix_date_time(31, "YEAR")
+-------------------------+
| from_unix               |
+-------------------------+
| 2001-01-01 00:00:00 UTC |
+-------------------------+

Need help using timestamp_from_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_from_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

You have a table storing Unix timestamps (integers representing seconds since 1970-01-01 00:00:00 UTC). You want to convert these timestamps into BigQuery TIMESTAMP format, but at different levels of granularity. Here are a few use cases:

  • Analyzing data by year: You have event data with Unix timestamps and you want to analyze trends year by year. You can use timestamp_from_unix_date_time(unix_timestamp, 'YEAR') to truncate the timestamps to the beginning of each year, then group your data by this truncated timestamp.
SELECT
  bigfunctions.us.timestamp_from_unix_date_time(event_timestamp, 'YEAR') AS event_year,
  COUNT(*) AS event_count
FROM
  `your_project.your_dataset.your_table`
GROUP BY
  event_year
ORDER BY
  event_year;
  • Generating reports by month: You want to create monthly reports based on user activity. You have user activity timestamps stored as Unix timestamps. Use timestamp_from_unix_date_time(unix_timestamp, 'MONTH') to get the beginning of the month for each activity, and then aggregate data accordingly.
SELECT
  bigfunctions.us.timestamp_from_unix_date_time(activity_timestamp, 'MONTH') AS activity_month,
  COUNT(DISTINCT user_id) AS active_users
FROM
  `your_project.your_dataset.user_activity`
GROUP BY
  activity_month
ORDER BY
  activity_month;
  • Data bucketing/aggregation: You want to group events into hourly buckets. You can use timestamp_from_unix_date_time(unix_timestamp, 'HOUR') to truncate timestamps to the beginning of each hour, enabling easy grouping and aggregation.
SELECT
  bigfunctions.us.timestamp_from_unix_date_time(event_timestamp, 'HOUR') AS event_hour,
  SUM(event_value) AS total_value
FROM
  `your_project.your_dataset.events`
GROUP BY
  event_hour
ORDER BY
  event_hour;
  • Simplifying date comparisons: Sometimes, you only care about the date part of a timestamp. Using timestamp_from_unix_date_time(unix_timestamp, 'DAY') effectively converts the Unix timestamp to a date, allowing for straightforward date comparisons without dealing with the time component.
SELECT *
FROM `your_project.your_dataset.events`
WHERE bigfunctions.us.timestamp_from_unix_date_time(event_timestamp, 'DAY') = '2024-03-15';

These examples demonstrate the flexibility of the function to handle different levels of time granularity based on the date_time_part argument, enabling a variety of time-based analysis and reporting tasks. Remember to replace your_project.your_dataset.your_table with your actual table information and the correct regional dataset for bigfunctions.

Spread the word

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

Share on Add a on