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