---
title: "UNIX_DATE Function in SQL: Syntax & Use | OWOX"
canonical: "https://www.owox.com/glossary/unix-date-function"
updated: "2026-03-11"
---

# What is the UNIX\_DATE function in SQL?

The UNIX\_DATE function converts a DATE value to an integer representing the number of days since the Unix epoch (1970-01-01). It’s commonly used in SQL (for example, in BigQuery) to normalize dates, simplify comparisons, and join tables that store dates as Unix-based integers for analytics and reporting.

**7 min** read · Updated March 11, 2026 · [SQL for Analytics](/blog/topics/sql)

The UNIX\_DATE function converts a DATE into an integer day number counted from the Unix epoch (1970-01-01), making dates easier to compare, join, and store consistently in analytics workflows.

## What is the UNIX\_DATE function?

UNIX\_DATE is a SQL date utility (commonly used in BigQuery-style SQL) that turns a calendar date like **2026-03-11** into a single integer that represents “how many whole days since 1970-01-01.” That’s it—no time of day, no timezone drama, no formatting quirks.

Why analysts love it: integers are fast to compare, easy to join on, and consistent across tools. If one table stores dates as DATE values and another stores an integer date key (often for dimensional modeling or legacy reasons), UNIX\_DATE becomes the bridge.

## How UNIX\_DATE works under the hood

### Unix epoch and day-based timestamps

The Unix epoch is the reference start point: **1970-01-01**. UNIX\_DATE calculates the number of day boundaries between your input date and that reference date.

Think of it as a “day index.” If your date is exactly 1970-01-01, the result is 0. If it’s the next day, the result is 1. Dates before 1970-01-01 produce negative numbers. That behavior is useful when you need consistent ordering or want to create rolling windows that don’t care about month lengths.

Crucially, UNIX\_DATE is **day-granular**. It is not a timestamp conversion. There is no time component to round or truncate—your input is already a date, and the output is a whole number of days.

### Supported input types and return type

UNIX\_DATE takes a **DATE** value as input and returns an **INT64** (an integer). In practice, your “DATE value” might come from:

*   A literal date (like DATE ‘2026-03-11’)
*   A DATE column
*   A conversion from other types (for example, converting a timestamp or string into DATE first, then applying UNIX\_DATE)

The output is always an integer day count. This is perfect for joining to date dimension keys, building partitions/buckets, or normalizing messy upstream date representations into a single comparable value.

## The Turning Point

OWOX Data Marts

See your first report built in real time. _15 minutes._

1.  Connect your data warehouse
2.  Pick your metrics
3.  Get a live Google Sheets report

In the time it takes to write a ticket. Then imagine never writing that ticket again.

[Book a Demo](/book-a-call)

We'll use your actual use case

## UNIX\_DATE syntax and basic usage

### Function syntax in SQL (BigQuery-style)

In BigQuery-style SQL, the basic form is straightforward: **UNIX\_DATE(date\_expression)**. The important part is that _date\_expression_ must evaluate to a DATE.

If you’re drafting queries quickly (or iterating on variations for cohorts, windows, and joins), it can help to speed up the “first draft” with tools or prompts for [generating SQL date queries with AI](/blog/articles/how-to-build-sql-queries-with-chatgpt)—then you validate the logic and edge cases like any responsible analyst.

### Simple examples with static and column values

Here are a few practical patterns you’ll actually use.

**1) Convert a static DATE literal to a Unix day integer:**

SELECT UNIX\_DATE(DATE ‘1970-01-01’) AS unix\_day;

**2) Convert a DATE column for normalization:**

SELECT order\_id, UNIX\_DATE(order\_date) AS order\_unix\_day FROM dataset.orders;

**3) Convert then compare using integers (useful when another table stores date keys as integers):**

SELECT \* FROM dataset.orders WHERE UNIX\_DATE(order\_date) BETWEEN 20000 AND 20030;

Even if you don’t memorize what day 20000 is, the integer range is stable and easy to compute from another date value in the same query.

## Practical use cases for analysts

### Joining fact tables with integer date keys

A classic: one dataset has a proper DATE column, another has an integer “date key” (often from a date dimension or an older warehouse design). UNIX\_DATE lets you join without messy string formatting or fragile “YYYYMMDD” hacks.

Common join pattern:

*   Fact table A: _event\_date_ as DATE
*   Fact table B: _date\_key_ as INT64 (days since epoch)

Then you join on: **UNIX\_DATE(event\_date) = date\_key**.

If you’re managing these relationships in a more governed way, it helps to understand [key constraints for managing date keys](/blog/articles/key-constraints-sql), especially when you want the warehouse to catch broken joins (like missing dates or duplicate keys) before your dashboard catches fire.

### Filtering and bucketing data by Unix date

UNIX\_DATE is great when you want stable buckets that don’t depend on month boundaries or localized formatting. For example:

*   **Rolling windows:** filter to “last 30 days” by comparing Unix day integers.
*   **Daily bucketing:** group by Unix day when your upstream data arrives as day keys.
*   **Incremental processing:** store the max processed unix day and resume from there.

Because the unit is “day,” your logic stays clean. You’re not worrying about hours, minutes, daylight savings shifts, or timestamp rounding. It’s just day arithmetic.

And if you’re encapsulating these patterns into reusable warehouse routines, you’ll often end up [using SQL functions inside stored procedures](/blog/articles/stored-procedures-sql-benefits-examples-use-cases) so the team can run consistent transformations without copying query fragments across notebooks and BI tools.

### Converting back from Unix date for reporting

Dashboards, exports, and stakeholder reports usually want human-readable dates, not “19743.” So you’ll often convert the integer back to a DATE for presentation or for joining to a full date dimension with attributes like week start, fiscal period, holiday flags, and so on.

In BigQuery-style SQL, you typically convert back by adding the integer day offset to the epoch date. Conceptually, it’s: epoch\_date + day\_offset. Once you’re back to a DATE, you can format it, group by month, or map it to reporting calendars.

One more real-world note: date fields sometimes become sensitive when combined with user-level data (think signup date, last purchase date, or “date of first seen”). If you’re sharing datasets broadly, look into [data masking for sensitive date-related fields](/blog/articles/data-masking-explained) so analysts can do their work without exposing what shouldn’t be exposed.

## Example: Using UNIX\_DATE in a marketing dashboard

### Preparing date keys for Data Marts

Scenario: you’re building a daily marketing performance dashboard. Your ad costs come in with a DATE. Your web analytics events land as timestamps. Your CRM exports a daily snapshot table keyed by an integer day number (days since 1970-01-01). You want one consistent “date key” across everything.

A practical approach is to create a single daily key field:

*   For tables with DATE: compute **UNIX\_DATE(date)**
*   For tables with TIMESTAMP: convert to DATE first, then **UNIX\_DATE(DATE(timestamp))**
*   For tables already using day integers: keep as-is, but validate it matches the same definition

This gives you a stable join key for your daily grain data mart. It also pairs nicely with dimensional modeling practices where a date dimension owns the definition of “day,” and facts point to it via a key. If you want a refresher on how those relationships should be structured, review [primary and foreign keys for date dimensions](/blog/articles/primary-and-foreign-keys-sql).

### Combining ad, web, and CRM data by Unix date

Let’s say you have:

*   **ads\_daily**(date DATE, campaign\_id STRING, cost NUMERIC)
*   **web\_events**(event\_timestamp TIMESTAMP, campaign\_id STRING, sessions INT64)
*   **crm\_daily**(unix\_day INT64, campaign\_id STRING, leads INT64)

You can normalize them to a shared key and combine at a daily, campaign level:

```
WITH ads AS (
SELECT
UNIX_DATE(date) AS unix_day,
campaign_id,
SUM(cost) AS cost
FROM ads_daily
GROUP BY 1, 2
),
web AS (
SELECT
UNIX_DATE(DATE(event_timestamp)) AS unix_day,
campaign_id,
SUM(sessions) AS sessions
FROM web_events
GROUP BY 1, 2
),
crm AS (
SELECT
unix_day,
campaign_id,
SUM(leads) AS leads
FROM crm_daily
GROUP BY 1, 2
)
SELECT
COALESCE(ads.unix_day, web.unix_day, crm.unix_day) AS unix_day,
COALESCE(ads.campaign_id, web.campaign_id, crm.campaign_id) AS campaign_id,
ads.cost,
web.sessions,
crm.leads
FROM ads
FULL OUTER JOIN web USING (unix_day, campaign_id)
FULL OUTER JOIN crm USING (unix_day, campaign_id);
```

The hype part: this is the kind of normalization that turns “three noisy feeds” into “one reliable daily truth table.” Your BI layer gets simpler, your joins get more predictable, and your comparisons across sources stop being a guessing game.

## UNIX\_DATE vs other date and time functions

### UNIX\_DATE vs UNIX\_SECONDS / UNIX\_MILLIS

UNIX\_DATE returns an integer count of **days**. UNIX\_SECONDS and UNIX\_MILLIS return integers based on **time** (seconds or milliseconds) since the epoch.

*   Use **UNIX\_DATE** when your reporting grain is daily and you want day-level keys.
*   Use **UNIX\_SECONDS / UNIX\_MILLIS** when you need time ordering within a day, event sequencing, latency calculations, or precise windows.

If you accidentally use seconds when you meant days, your joins will fail silently (no matches) or explode cardinality (too many matches). So pick the function that matches your grain.

### UNIX\_DATE vs standard DATE functions

Standard DATE functions help you extract parts (year, month, day), truncate (to week/month), add/subtract intervals, and format dates for display. UNIX\_DATE is different: it’s a normalization move that converts a DATE into a single comparable integer.

In practice, they work together:

*   Use DATE functions to define the business logic (like “week starts Monday” or “month to date”).
*   Use UNIX\_DATE to create stable keys and simplify joins/filters where integers are expected.

If your warehouse design leans on a date dimension, you may only need UNIX\_DATE at ingestion/standardization time, and then rely on the date dimension for everything else downstream.

## OWOX Data Marts context note

In data mart workflows, UNIX\_DATE is a handy way to enforce a single daily key across sources that disagree on date formats or storage types. Whether your upstream tables arrive with DATEs, timestamps, or legacy integer day keys, the goal is the same: one clean join field that makes daily reporting fearless.

Want to build clean, join-ready reporting tables faster? Try [OWOX Data Marts](https://www.owox.com/app-signup) to structure your warehouse outputs for analysis, and keep your [data marts](https://www.owox.com/app-signup) consistent as your sources grow.

## Topics

[Glossary](/glossary)

[SQL for Analytics](/blog/topics/sql)

## Related terms

[ARRAY\_REVERSE in SQL](/glossary/array_reverse-sql)

[ARRAY\_CONCAT in SQL](/glossary/array-concat-sql)

[ARRAY\_LENGTH in SQL](/glossary/array-length-sql)

[ARRAY in SQL](/glossary/array-sql)

## Related articles

## Learn more about analytics

[sql · Primary and foreign keys in SQL: what your warehouse enforces · Oct 19, 2026](/blog/articles/primary-and-foreign-keys-sql)

[sql · Understanding key constraints in SQL for data integrity · Apr 28, 2025](/blog/articles/key-constraints-sql)

[sql · Stored procedures in SQL explained: Benefits, examples, and use cases · Apr 14, 2025](/blog/articles/stored-procedures-sql-benefits-examples-use-cases)

## Customer stories

## Learn how teams ship analytics faster

Organizations that scaled analytics without scaling headcount

[All case studies →](/blog/success-stories)

[Pürblack · "For 10 years I was blind." The day Pürblack® founder stopped guessing · Seconds · to get reports across six channels · Read the story](/blog/success-stories/purblack)

[Reformation · How OWOX Reports Helped Reformation Make Data-Backed Decisions · Minutes · from data request to business decision · Read the story](/blog/success-stories/reformation)

[WorkSimpli · How OWOX Reports Streamlined Operations for WorkSimpli, Saving Over 10 Hours Weekly · 10hrs+ · saved per week on manual reporting · Read the story](/blog/success-stories/worksimpli)

## What users are saying

## Not testimonials. Comment threads.

Real things real customers said — each quote pinned to a specific claim, straight from the quotes database.

A3 · re: trusting AI

![Nodari Rizun](https://cdn.owox.ai/www/webflow/6a7b298b2ca5055443187566_nodari-headshot.png/public) Nodari Rizun · Founder & CEO, Pürblack®

> _“AI, by the nature of the models, will hallucinate. And because of that, you need something which will create guardrails to ensure that there are no hallucinations, that you can trust your data.”_

C5 · re: opened eyes

![Nodari Rizun](https://cdn.owox.ai/www/webflow/6a7b298b2ca5055443187566_nodari-headshot.png/public) Nodari Rizun · Founder & CEO, Pürblack®

> _“I was blind, now I can see. OWOX opened our eyes.”_

E9 · re: results and support

PandaDoc · Analytics team

> _“We are extremely satisfied with the results achieved through our partnership with OWOX. I'm also impressed by quick and effective support we get from OWOX”_

## Your New Normal

Turn your data into decisions.

Governed data marts give you the clean foundation ML needs to actually work.

*   No AI hallucinations
*   Analyst-governed definitions
*   Every number traces to SQL

[Get started free](https://www.owox.com/app-signup)

## Book a call

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Schedule a meeting

## Schedule in person meeting at Tip Top Toronto

You are booked. The invitation is on its way to , with the calendar invite and the meeting link.

[Reschedule](#)

[Cancel](#)

## Links

Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link.

- [SQL for Analytics](https://www.owox.com/blog/topics/sql) — /blog/topics/sql.md
- [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md
- [generating SQL date queries with AI](https://www.owox.com/blog/articles/how-to-build-sql-queries-with-chatgpt) — /blog/articles/how-to-build-sql-queries-with-chatgpt.md
- [key constraints for managing date keys](https://www.owox.com/blog/articles/key-constraints-sql) — /blog/articles/key-constraints-sql.md
- [using SQL functions inside stored procedures](https://www.owox.com/blog/articles/stored-procedures-sql-benefits-examples-use-cases) — /blog/articles/stored-procedures-sql-benefits-examples-use-cases.md
- [data masking for sensitive date-related fields](https://www.owox.com/blog/articles/data-masking-explained) — /blog/articles/data-masking-explained.md
- [primary and foreign keys for date dimensions](https://www.owox.com/blog/articles/primary-and-foreign-keys-sql) — /blog/articles/primary-and-foreign-keys-sql.md
- [OWOX Data Marts](https://www.owox.com/app-signup)
- [Glossary](https://www.owox.com/glossary) — /glossary.md
- [ARRAY_REVERSE in SQL](https://www.owox.com/glossary/array_reverse-sql) — /glossary/array_reverse-sql.md
- [ARRAY_CONCAT in SQL](https://www.owox.com/glossary/array-concat-sql) — /glossary/array-concat-sql.md
- [ARRAY_LENGTH in SQL](https://www.owox.com/glossary/array-length-sql) — /glossary/array-length-sql.md
- [ARRAY in SQL](https://www.owox.com/glossary/array-sql) — /glossary/array-sql.md
- [All case studies →](https://www.owox.com/blog/success-stories) — /blog/success-stories.md
- [Pürblack · "For 10 years I was blind." The day Pürblack® founder stopped guessing · Seconds · to get…](https://www.owox.com/blog/success-stories/purblack) — /blog/success-stories/purblack.md
- [Reformation · How OWOX Reports Helped Reformation Make Data-Backed Decisions · Minutes · from data…](https://www.owox.com/blog/success-stories/reformation) — /blog/success-stories/reformation.md
- [WorkSimpli · How OWOX Reports Streamlined Operations for WorkSimpli, Saving Over 10 Hours Weekly ·…](https://www.owox.com/blog/success-stories/worksimpli) — /blog/success-stories/worksimpli.md