CODEWITHSUNDEEP
ruby-on-railspostgresql
medBouzid
medBouzid

Reputation: 8382

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

I have a jsonb field where I store a token expires date like this:

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

when I do 

user.token['token_expires']
=> "2018-09-23T18:21:58.010Z"

The result is 2018-09-23T18:21:58.010Z

if I check the current datetime in the console 

Time.now.utc
=> 2018-09-23 19:06:20 UTC

The result is 2018-09-23 19:06:20 UTC

Now the issue is when I try to run the following request :

User.where("""users.token ->> 'token_expires' <= :current_time""",
                                           current_time: Time.now.utc).pluck(:id)
=> []  # returns an empty array/result

So the token expires date is 2018-09-23T18:21:58.010Z and the current datetime is 2018-09-23 19:06:20 UTC but in the request, when I try to get users who have token_expires < Time.now.utc I got nothing! what am expecting is to get the user I created the first time

Upvotes: 0

Views: 1048

Answers (1)

medBouzid
medBouzid

Reputation: 8382

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

Upvotes: 1

Related Questions