Published on

# How to store timestamps in Rails

Authors

Photo by Jesse Blom on Unsplash

# Introduction

If you are a Rails developer, you probably know, that Rails operates on UTC by default. But what exactly does it mean? UTC states for Coordinated Universal Time.

As wikipedia says:

Coordinated Universal Time or UTC is the primary time standard by which the world regulates clocks and time. It is within about 1 second of mean solar time at 0° longitude and is not adjusted for daylight saving time. It is effectively a successor to Greenwich Mean Time (GMT).

I remember that when I was in school, time was often presented as an endless straight line and every element on it happened exactly once:

That simple metaphor shows us exactly what is an absolute notion of time.

But why I am talking about absolute time instead of just time?

The problem with time is that we operate daily is that it is "clock time". And it is regulated by law of the country that we are currently in. And this clock time would be different for people in different time zones. So it is possible that me(located in Wrocław, Poland) and my friend from London at the same moment will be placed on two different places on that timeline. What is even more, clock time allows us to travel back in time: 😮

When we change from summer to winter time, the clock would be set back by one hour. So clock time is basically a mapper of absolute time, that is flowing steadily forward on the timeline. UTC represents that absolute time.

But no one tells the time in UTC on a daily basis, so if we keep the data in UTC, we have to map it to the clock time. To achieve this, we use time zones. I am currently in Poland, and we use CET(Central European Time) at the time of writing this article, so I will basically add 1 hour to UTC time. We can think of time zones as a mapper that can map time clock to absolute time (UTC in our case) and other way round time clock to UTC.

### How this applies to Rails?

As I said at the beginning, Rails operates on UTC by default.

activerecord/lib/active_record.rb

200  self.default_timezone = :utc

activerecord/lib/active_record/railtie.rb

75  initializer "active_record.initialize_timezone" do76    ActiveSupport.on_load(:active_record) do78      self.time_zone_aware_attributes = true79    end80  end

This indicates that when attributes are read from the database, they will be converted into the time zone specified by Time.zone (UTC by default).

But how does Rails store datetime in database? I f we will look into database structure, we will see that by default rails creates datetime columns as timestamp without time zone.

created_at timestamp(6) without time zone NOT NULL,updated_at timestamp(6) without time zone NOT NULL

#### What does it mean?

PostgreSQL supports two types of timestamps, one with and one without time zone. So we can store datetime type with and without timezone in database, right?

Not exactly, these types are exactly the same:

Name Storage Size Description Low Value High Value Resolution
timestamp [ (p) ] [ without time zone ] 8 bytes both date and time (no time zone) 4713 BC 294276 AD 1 microsecond
timestamp [ (p) ] with time zone 8 bytes both date and time, with time zone 4713 BC 294276 AD 1 microsecond

Timestamp with time zone does not store the time zone.

Why we have two different types of timestamps if they are stored in the same way?

For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.

When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone.

Postgresql docs

In simple words when we see timestamptz, we can be sure that data stored in these columns is in UTC. When we see timestamp we are not in such comfortable position, we can only guess and hope that it is in UTC, and for example not in a developer local time.

#### Why we should take care of this if Active Record converts a timestamp to UTC before storing it in the DB?

We can compare this situation to rails validations and constraints on database level. It is good to validate data, but we can be sure that we do not have any inconsistency in our Database only if we also have database level validations.

Why?

For example:

• we can omit validations
• external service can write to database directly
• our code can override validations

The same applies for timestamps:

• our code can override this
• Rails can override this
• connection to the database can override this
• external service is not obligated to store timestamps in UTC

#### My recommendation

I would suggest to always use TIMESTAMP WITH TIME ZONE when we store datetime that are points on our timeline. When it comes to moments in time, I will still use TIMESTAMP WITHOUT TIME ZONE.

For example, when we would like to store future appointments, I will use TIMESTAMP WITHOUT TIME ZONE. Any future appointment is just a potential moment on our timeline, so we are not able to apply timezone on it.

For example, if we store information, that factory workers needs to start work at 8 a.m. This information does not indicate any specific moment in time. If we store information about what time each employee started to work on a particular date in Pekin, Warsaw, Berlin, Detroit it will become a point on our timeline and to map it correctly back to the user we will need timezone.

But for any other use case, I would use TIMESTAMP WITH TIME ZONE.

What is more, we can set this as default timestamp time in Rails.

# config/initializers/timestamps.rbrequire "active_record/connection_adapters/postgresql_adapter.rb"ActiveRecord::ConnectionAdapters::PostgreSQLAdapter::NATIVE_DATABASE_TYPES[:datetime] =  {    name: "timestamptz"  }

With this initializer, t.timestamps or t.datetime will use TIMESTAMP WITH TIME ZONE by default.

But remember to always specify a time zone when inserting data.

Apart from this, I recommend to:

• Set timezone = 'UTC' in postgresq.conf -> UTC will be default time zone for all connections
• if you will use only one timezone in our app, we can set SET timezone = 'TIME_ZONE' on connection and all values from database will be retrieved in this time zone.