How should constant_sql_override extension be best used for UTC timezone?

[bkuhlmann]

Overview

Hello. 👋 I was trying to load the constant_sql_override extension and then use it to ensure the current time zone defaults to UTC but keep getting the following error when running migrations:

ERROR:  syntax error at or near "AT" (PG::SyntaxError)

I'm not sure if this is a bug or if I'm using the extension incorrectly (probably the latter).

Steps to Recreate

Here's, roughly, what I'm using for my setup:

Sequel::Database.extension :constant_sql_override, :pg_enum
Sequel.database_timezone = :utc
Sequel.application_timezone = :local

database.set_constant_sql Sequel::CURRENT_TIMESTAMP, "CURRENT_TIMESTAMP AT TIME ZONE 'UTC'"

I'm basically following the extension's documentation. As for my migrations, I'm writing code like this (which should default to UTC):

column :created_at, :timestamp, null: false, default: Sequel::CURRENT_TIMESTAMP

Lastly, if it helps, I'm wiring this up using Hanami via the persistence provider. If you need to see the full picture, you'd apply the diff above to this provider code to see how I'm wiring this all together.

If there is any additional information I can provide, let me know. Thanks!

Environment

• Ruby: ruby 3.2.1 (2023-02-08 revision 31819e82c8) +YJIT [arm64-darwin22.3.0]
• PG: 1.4.6
• Sequel: 5.66.0

[jeremyevans]

When you use CURRENT_TIMESTAMP in that context (as a default column value), you need to make sure it is wrapped in parentheses. This should work:

set_constant_sql Sequel::CURRENT_TIMESTAMP, "(CURRENT_TIMESTAMP AT TIME ZONE 'UTC')"

Alternatively, you could keep the constant SQL, and change the default option:

column :created_at, :timestamp, null: false, default: [Sequel::CURRENT_TIMESTAMP]

[bkuhlmann]

I went with the first suggestion and see this working now. Thanks!