Using current time in UTC as default value in PostgreSQL

Using current time in UTC as default value in PostgreSQL

Hey there! 👋 Are you trying to set the default value of a TIMESTAMP WITHOUT TIME ZONE column to the current time in UTC using PostgreSQL? 🤔 Well, you've come to the right place! In this blog post, we'll walk you through the common issues you might encounter and provide you with easy solutions to make your life easier. Let's dive in! 🚀

The Challenge 🤷‍♂️

So, you want to set the default value of your TIMESTAMP WITHOUT TIME ZONE column to the current time in UTC. Sounds easy, right? 💡 You might think that using the now() function with the at time zone 'utc' expression would do the trick. Unfortunately, it's not that straightforward. 😔

The Issue 😓

Let's take a look at an example to illustrate the problem. Here's how you would get the current time in UTC using the now() function in PostgreSQL:

postgres=# SELECT now() AT TIME ZONE 'utc';
          timezone          
----------------------------
2013-05-17 12:52:51.337466
(1 row)

As you can see, running this query gives you the current time in UTC. 😌

Now, if you try to set the default value of your TIMESTAMP WITHOUT TIME ZONE column to the current time in UTC, like this:

postgres=# CREATE TEMPORARY TABLE test(id int, ts timestamp without time zone default now() AT TIME ZONE 'utc');
ERROR:  syntax error at or near "AT"
LINE 1: ...amp without time zone default now() AT TIME ZONE 'utc');

Oops! 😬 You receive a syntax error! That's not what we were expecting. 😕

The Solution 🙌

But don't worry, we've got your back! 💪 Here's an easy solution to work around this syntax error and achieve what you want. 😉 Instead of trying to set the default value using the now() function with the AT TIME ZONE expression, you can use the current_timestamp function, which returns the current timestamp in the session time zone. Then, you can convert it to UTC using the AT TIME ZONE 'utc' expression. Pretty cool, right? 😎

Let's see how it's done:

postgres=# CREATE TEMPORARY TABLE test(id int, ts timestamp without time zone default current_timestamp AT TIME ZONE 'utc');
CREATE TABLE
postgres=# INSERT INTO test VALUES (1) RETURNING ts;
             ts             
----------------------------
2013-05-17 14:54:33.072725
(1 row)

Voilà! 🎉 Now, the default value of the ts column is set to the current time in UTC. Mission accomplished! 🙌

The Call-to-Action 📢

We hope this blog post was helpful to you! If you have any questions or any other PostgreSQL-related topic you'd like us to cover, please don't hesitate to reach out to us. We love hearing from our readers! ❤️

So go ahead and leave a comment down below. Share your thoughts, experiences, or any related tips and tricks you have. Let's start a discussion and help each other out! 🗣️💡

Until next time, happy coding! 😄✨