Using Utc with Sequel

Using UTC with Sequel?

The easiest way to try to put them in as UTC is to override literal_datetime and/or literal_time for the dataset class you are using to return a literal string of the UTC time.

Getting them out in UTC depends on the adapter you are using. For example, the postgres adapter calls Sequel.string_to_datetime, which just calls parse on the Sequel.datetime_class (Time by default). If the datetime column includes the timezone information, things should work fine. If it doesn't include timezone information, Time.parse is going to assume it is given a local time. In that case, you may want to override Sequel.string_to_datetime to make sure it always returns the time with the UTC offset (maybe by calling Time.parse(s).gmtime).

How to make TIMESTAMP WITH TIME ZONE column with Ruby Sequel?

When creating the table, you can specify specific types:

db.create_table :TestTable1 do
  column :field1, 'timestamp with time zone'
end

You probably want to use Sequel.default_timezone = :utc as well, but that doesn't affect how times are actually stored in the database.

Reading and Writing UTC to TIMESTAMP in Postgresql

You could set the timezone of your RDBMS to UTC, see https://medium.com/building-the-system/how-to-store-dates-and-times-in-postgresql-269bda8d6403

When that's done, whatever dates you store, they will be in UTC. Converting from UTC into something else can be done either in queries, like

select created_at at time zone 'utc' at time zone 'america/los_angeles'
from users;

Taken from https://popsql.com/learn-sql/postgresql/how-to-convert-utc-to-local-time-zone-in-postgresql

Or, you can convert the timezone at application level.

How to convert output timezone per query in sequel ruby?

You probably want to use Sequel's thread_local_timezones extension: http://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/thread_local_timezones_rb.html

This is per thread, not per query, but should hopefully still work for your needs.

Why does timezone info from Ruby sequel and Postgres psql differ?

The column is of type 'timestamp without timezone'. Thus when Postgres displays a value in this column it just displays the timestamp with no timezone. However, Sequel wants to convert a Postgres timestamp to an instance of the Ruby Time class, and an instance of the Time class must have a timezone specified - either it's a time in the local timezone or it's a time in UTC. Thus Sequel must choose one. By default, it's choosing your local timezone.

You may configure the database and application timezone in Sequel. See https://www.rubydoc.info/gems/sequel/4.36.0/Sequel/Timezones

For example, here's the default Sequel behavior with a database I had handy:

>   c['select * from actors'].each do |row|; puts row[:created_at]; end
Thu Jul 12 20:33:17 -0400 2012

Here the timestamp is assumed to be in my local timezone (EDT).

However, if I do this:

> Sequel.database_timezone = :utc
 => :utc 
> c['select * from actors'].each do |row|; puts row[:created_at]; end
Thu Jul 12 20:33:17 UTC 2012

Then the timestamp is assumed to be in UTC.

Is java.sql.Timestamp timezone specific?

Although it is not explicitly specified for setTimestamp(int parameterIndex, Timestamp x) drivers have to follow the rules established by the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) javadoc:

Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom time zone. If no Calendar object is specified, the driver uses the default time zone, which is that of the virtual machine running the application.

When you call with setTimestamp(int parameterIndex, Timestamp x) the JDBC driver uses the time zone of the virtual machine to calculate the date and time of the timestamp in that time zone. This date and time is what is stored in the database, and if the database column does not store time zone information, then any information about the zone is lost (which means it is up to the application(s) using the database to use the same time zone consistently or come up with another scheme to discern timezone (ie store in a separate column).

For example: Your local time zone is GMT+2. You store "2012-12-25 10:00:00 UTC". The actual value stored in the database is "2012-12-25 12:00:00". You retrieve it again: you get it back again as "2012-12-25 10:00:00 UTC" (but only if you retrieve it using getTimestamp(..)), but when another application accesses the database in time zone GMT+0, it will retrieve the timestamp as "2012-12-25 12:00:00 UTC".

If you want to store it in a different timezone, then you need to use the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) with a Calendar instance in the required timezone. Just make sure you also use the equivalent getter with the same time zone when retrieving values (if you use a TIMESTAMP without timezone information in your database).

So, assuming you want to store the actual GMT timezone, you need to use:

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
stmt.setTimestamp(11, tsSchedStartTime, cal);

With JDBC 4.2 a compliant driver should support java.time.LocalDateTime (and java.time.LocalTime) for TIMESTAMP (and TIME) through get/set/updateObject. The java.time.Local* classes are without time zones, so no conversion needs to be applied (although that might open a new set of problems if your code did assume a specific time zone).

comparing a datetime from sql request and Time.now.utc

I will answer my question here hopefully someone find it useful

What everyone needs to understand is that date or datetime is stored in json/jsonb field as a STRING, and generally what happened with my code

user.token = {token_expires: Time.now.utc}
user.save

is that Rails implicitly convert Time.now.utc to iso8601 which get stored as a string and that's the reason why when I do (in my console)

user.token['token_expires']

I get "2018-09-23T18:21:58.010Z" which is the iso8601 format of my previous Time.now.utc

Am not going to get into explaining why Rails do this conversion, but from what I understood is that it's recommended to use ISO-8601 format to store date/datetime in the database especially for json/jsonb fields, because it make it easy to deal with date or when working with APIs

Can you store other format than the ISO-8601 ?
Yes you can, it depends on your needs for example I was be able to prevent Rails from doing the implicit conversion for me by passing a string in my code instead of datetime like this :

user.token = {token_expires: Time.now.utc.to_s}
user.save

and the stored datetime (the string) will looks like this: 2018-09-23 18:21:58 UTC

You might want to store the date as unix time, it's up to you!

So back to my problem and the query that doesn't return anything, my query was like the following:

User.where("""users.token ->> 'token_expires' <= :current_time""",
                                       current_time: Time.now.utc).pluck(:id)

Here in the sql query the Time.now.utc (current_time) that I compare with becomes something like this 2018-09-23 22:21:18.952113 while the values in the database (jsonb field) have different format (iso8601) that's the reason why I got nothing as a result.

So to solve the problem either I had to store the current time (Time.now.utc) as a string by explicitly convert it using .to_s Time.now.utc.to_s and prevent Rails from converting it to iso...

The second solution is to convert the iso datetime to timestamp format in the SQL query like this:

User.where("""(users.token ->> 'token_expires')::timestamp <= :current_time""",
                                       current_time: Time.now.utc).pluck(:id)

Here is other related questions that helped me get to this conclusion:

Is ISO8601 the best date-format for PostgreSQL jsonb when i want to filter by the date?

JSONb dates: actual dates internally?

Converting UTC timestamp to ISO 8601 in Ruby

SQLite and inserting the current date in UTC format

SELECT DATETIME('now') returns the current UTC datetime. See Date And Time Functions. You can use DATETIME DEFAULT CURRENT_TIMESTAMP with column declaration.

Format 11, the string 'now', is
converted into the current date and
time as obtained from the xCurrentTime
method of the sqlite3_vfs object in
use. Universal Coordinated Time (UTC)
is used

For the 'modified' field you can use a trigger.

---

Related Topics