“...I've been working since 2008 with Ruby / Ruby on Rails, love a bit of Elixir / Phoenix and learning Rust. I also poke through other people's code and make PRs for OpenSource Ruby projects that sometimes make it. Currently working for InPay who are based in Denmark...”

Rob Lacey
Senior Software Engineer, UK

Ruby Time#to_i and MySQL UNIX_TIMESTAMP

In a recent project I wanted to use a date to be a component of a hashed string in order to create a unique hash for a password reset page. I want the hash to expire when the user’s password is reset, in this case when the user’s last_logged_in datetime changes. I can generate the hash simply.

class User < ActiveRecord::Base
....
  def password_token
    unless new_record?
      Digest::SHA1.hexdigest([id, password_hash, salt, last_logged_in.to_i].join)
    end
  end
....
end

I don’t want to store this hash in the database (we might want to in the future to optimise the look up). So I need to generate the hash in SQL, but how to I do the equivalent to Time#to_i in SQL.

[29] pry(main)> Time.parse('2014-10-03 01:17:21 +0100').to_i
=> 1412295441

I initially thought I was looking for TO_SECONDS as per the MySQL Date and Time functions it seems it should be UNIX_TIMESTAMP

mysql> SELECT TO_SECONDS('2014-10-03 01:17:21 +0100');
+-----------------------------------------+
| TO_SECONDS('2014-10-03 01:17:21 +0100') |
+-----------------------------------------+
|                             63579518241 |
+-----------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2014-10-03 01:17:21 +0100');
+---------------------------------------------+
| UNIX_TIMESTAMP('2014-10-03 01:17:21 +0100') |
+---------------------------------------------+
|                                  1412295441 |
+---------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Found the answer at HOW TO GET THE CURRENT EPOCH TIME . And now I can write the finder method.

class User < ActiveRecord::Base
....
  class << self
    ....
    def with_password_token(password_token)
      where("SHA1(CONCAT(users.id, IF(users.password_hash IS NOT NULL, users.password_hash,''), IF(users.salt IS NOT NULL, users.salt, ''), IF(users.last_logged_in, UNIX_TIMESTAMP(users.last_logged_in), ''))) = ?", password_token).first
    end
    ....
  end
....
end