Get Expert Help

Quick Base Junkie Blog

Short videos and posts to skyrocket your Quickbase skills!

Get tips in your inbox

Join the Quick Base Junkie Community email list!

Why I won't use this Jinja Template in Pipelines

intermediate jinja pipelines Jun 09, 2021

Using Jinja in my Pipelines has been a game-changer!

Hands down, writing Jinja templates is one of the best skills I've learned.


There's one "popular" template that I refuse to use.

It's the {{ }} template.

While it may seem like a wonderful way to shortcut getting today's date into your Pipeline... it's evil in disguise! 😈

And before I explain myself, if you have no idea what Jinja is watch & read this first.

Why is this a problem?

Let me break it down for you...

The {{ }} template (or anywhere is used) will result in the current date in UTC time.

What is UTC time?

UTC stands for Coordinated Universal Time and is the time standard that all time zones measured from.

For me, I'm in the Pacific Time zone which is currently 7 hours behind UTC.

What does this have to do with Jinja?

The time templates, {{ }} and {{ }}, both use UTC.

But, only one of them can be adjusted to the local time zone!

The {{ }} template is set at the zero hour and cannot be adjusted, therefore it will always have the wrong date whenever it's past midnight in UTC time.

Right now for me, this is any time after 5 PM.

So if I have a pipeline that runs after 5 PM with the {{ }} template... I WILL GET THE WRONG RESULT.

How is this possible?

Here's the deal...

Say it's the 10th of the month after 5 PM and a Pipeline is triggered to create a new Quickbase record with a "Start Date" field set to 2 days in the future.

That "Start Date" should be the 12th.

However, if this is the template I'm using for "Start Date"

{{ + }}

I'll end up with the 13th, because it's interpreting today as the 11th (and not the 10th).


But it can't always be wrong, can it? 

For the portion of the day that your time zone is the same day as the UTC time zone, you won't see any issues... but I'm not willing to take the risk.

Also, it doesn't matter what type of field you're putting the date into.

Here's an example:

When the actual (Pacific Time Zone) Date/Time is June 10, 2021 5:32 PM...

Date field

Result: 06-11-2021

This is the most common use for this template, and the result is off by 1 day.

Date/Time field

Result: 06-10-2021 05:00 PM (PDT)

While it wouldn't really make sense to use this template with a date/time field, the result is just weird. Quickbase adjusts the zero hour in UTC by the 7 hr offset, resulting in a 5:00 PM time vs ignoring the zero hour as 'date only'.

Time of Day field

Result: Error

It also wouldn't make much sense to use this template with a Time of Day field, because the time associated with it is 00:00 (effectively midnight). Also this field acts more like a text field and requires the inputs be formatted as such in the HH:MM AM/PM format. Because of this, no adjustments are automatically made for the time zone. 

Text field

Result: 2021-06-11 00:00:00+00:00

In a text field, we can see the date is off by 1 day and the time+offset is set to zero.

So, what do I use instead?

In place of using {{ }}, I use {{ }}... each time every time.

The {{ }} template also returns the current date and time in UTC.

HOWEVER, because it has a time associated with it, it can be adjusted to the proper local time zone.


{{ }}

This field type will account for the time zone difference automatically, there is no need to make changes to the template.


{{ (|timezone('America/Los_Angeles')).date() }}

This requires 2 adjustments, one to set the time zone and the other to convert it into a date only so that the time zone doesn't change back when it's inserted into the field.

You can learn about using timezone in this post: Time Zone Troubles? Fix it with Jinja

And going back to the earlier "Start Date" example, here's that updated template:

{{ (|timezone('America/Los_Angeles')).date() + }}

For more on adjusting dates, check out 2 Ways to Adjust Dates and Times Using Jinja.

Time of Day

{{ (|timezone('America/Los_Angeles')).strftime('%I:%M %p') }}

As mentioned earlier, this field acts more like a text field in Pipelines. After adjusting for the time zone, it's also necessary to format the time as a text string.

Here I'm using strftime to get the right text format for the field.


{{|timezone('America/Los_Angeles') }}

Use the timezone to get the proper date/time.

{{ (|timezone('America/Los_Angeles')).strftime('%d-%m-%Y %I:%M') }}

Use strfime to format it in a user friendly format. For more on strftime, check out the FREE Jinja Date & Time Cheat Sheet.



Where can I learn more about Jinja?

Jinja can be super helpful when you know how to use it (properly).

If this post has you curious about other date/time functions in Jinja, download my FREE Jinja Date & Time Cheat Sheet.

And for more in-depth training on Jinja, plus dozens more templates, tricks, and strategies, I highly recommend the Intro to Jinja for Pipelines course.


Feeling like a Junkie?  Subscribe Now!