This pattern can be adjusted to accommodate different week start days, but will need to be adjusted if your database assigns day of week index numbers differently to week days. Putting all the expressions together, we subtract the Saturday count and Sunday count from the first naive count to calculate the number of weekdays elapsed in the time range.

This third piece counts the number of fully completed weeks, which will be the same as the number of Sundays in that range (there is one Sunday per week). postgres> CREATE FUNCTION DateDiff (units VARCHAR(30), startt TIMESTAMP. However, if the last day is a Saturday, then it gets bumped to a Sunday, and this count will increase. Hi, I created a generic function (DATEFIFF) login to postgres database using psql. This expression checks by calculating how many weeks there are in this range if the last day is forwarded by one day? For example, if the last day is a Friday, then it moves to Saturday, and that partial week doesn't count towards our total because DATEDIFF('week'. Since there can never be fewer Saturdays than Sundays in a given range (because Sunday is after Saturday), we will check whether the last day in the range is a Saturday. For example, assume there is a table: CREATE TABLE licenses ( purchased DATE, valid INT ) - Insert an item purchased today, valid 31 days INSERT INTO licenses VALUES ( CURRENTTIMESTAMP, 31) Now to get the expiration date you can use the following. The following example is for Snowflake, and will need to be updated as necessary for your specific SQL dialect. In PostgreSQL, you can use the following expression: datetime + variable INTERVAL '1 day'. You will also want to make sure you have the date timeframe declared in the dimension_groups that will represent your start date and end date. Note:You will want to make sure your database will return a day of week index of 0 for Sunday and 6 for Saturday this is important, or the calculations will be one or two days off. But, there is a more straightforward way, using a single short query, which you can define in the sql parameter of a dimension or measure in LookML. One way of achieving this is to create a derived table with the necessary logic. What if you want to exclude weekend days (Saturday and Sunday)? However, the functions above will count all the days between the two specified dates. Snowflake: DATEDIFF('day', start, stop) AS days Postgres: DATEPART('day', start - stop) AS days Redshift: DATEDIFF('day', start, stop) AS days

testdb SELECT AGE(timestamp '', timestamp '') The above given PostgreSQL statement will produce the following result. Example of the function AGE (timestamp, timestamp) is.

If you'd like to count the days between two dates, you could use the SQL DATEDIFF function, which simply counts the days between two points in time: AGE (timestamp) When invoked with only the TIMESTAMP as argument, AGE () subtracts from the currentdate (at midnight). Follow these seven use case examples for business. Looker will not be updating this content, nor guarantees that everything is up-to-date. PostgreSQL date functions (like DATETRUNC, EXTRACT, and AGE) make wrangling timestamps much easier.

Postgresql datediff