xmetricFunctions

Tableau function Operators

Tableau - (date, date) to SQL

[d] - [e] returns the number of days between two date-times, including the fraction: 36 hours is 1.5. In DuckDB, subtract epochs and divide by 86,400. date_diff('day', e, d) answers another question, the number of midnights crossed, and rounds the fraction away.

Updated · Checked on the xmetric engine

What is the syntax?

date - date
date
two dates or date-times; the result is positive when the first is later.

Returns a number of days, fractional for date-times. Null if either side is null.

What does it return, and what is the SQL?

Each example runs on the same 8 sample rows twice: the Tableau formula on the xmetric engine, and the SQL in DuckDB.

How many days passed between e and d?

Tableau

[d] - [e]

SQL (DuckDB)

SELECT (epoch(d) - epoch(e)) / 86400 AS result
FROM input
ORDER BY id

Row 1: 36 hours is 1.5 days. Row 2: one hour is 0.04.

deResult
2024-02-28 18:002024-02-27 06:001.5
2024-02-29 00:002024-02-28 23:000.04
2024-03-01 09:302024-02-28 09:302
2025-01-01 12:002024-12-31 00:001.5
2024-01-01 00:00null
2024-01-01 08:00null
2024-12-31 23:002024-01-31 00:00336
2024-06-15 10:002024-06-15 10:000

The Tableau formula and the SQL return the same 8 rows.

Where does a straight translation go wrong?

Common mistake

Why does date_diff('day') count one day for an hour?

date_diff counts midnights crossed: 23:00 to 00:00 is 1. Tableau's - measures elapsed time: 0.04 days.

Tableau

[d] - [e]

Tempting SQL

SELECT date_diff('day', e, d) AS result
FROM input
ORDER BY id

SQL that matches

SELECT (epoch(d) - epoch(e)) / 86400 AS result
FROM input
ORDER BY id
deTableauTempting SQLSQL that matches
2024-02-28 18:002024-02-27 06:001.511.5
2024-02-29 00:002024-02-28 23:000.0410.04
2024-03-01 09:302024-02-28 09:30222
2025-01-01 12:002024-12-31 00:001.511.5
2024-01-01 00:00nullnullnull
2024-01-01 08:00nullnullnull
2024-12-31 23:002024-01-31 00:00336335336
2024-06-15 10:002024-06-15 10:00000

The SQL that matches returns the Tableau result on every row; the marked cells are where the tempting SQL differs.

What if both sides are dates?

Converting to dates first gives whole days. Columns 1 to 8 are the sample rows, by id.

CaseTableau, then SQL (DuckDB)12345678
dates onlyDATE([d]) - DATE([e])CAST(d AS DATE) - CAST(e AS DATE)1121nullnull3350

On every row, each Tableau formula and its SQL return the same value.

How do I write it in Snowflake or BigQuery?

The first example, written for each warehouse from its documentation. These are not run here; DuckDB is.

Snowflake

SELECT DATEDIFF(second, e, d) / 86400 AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT DATETIME_DIFF(d, e, SECOND) / 86400 AS result
FROM input
ORDER BY id

BigQuery documentation

What does your own - (date, date) formula return?

Edit the formula and run it on the 8 sample rows. Fields: [id], [a], [b], [x], [p], [q], [s], [t], [d], [e].

Runs in your browser on the xmetric engine. Nothing is uploaded.

Which functions go with it?

What do people ask about - (date, date)?

What if both are dates without a time?

The result is a whole number of days: DATE([d]) - DATE([e]).

How is it different from DATEDIFF?

DATEDIFF counts boundaries of a date part; - measures elapsed days.

Where is this documented?