Is there a way to change Rails default timestamps to Y-m-d H:i:s (instead of Y-m-d H:i:s.u) or have laravel ignore decimal portion of Y-m-d H:i:s.u?

Question

I have two applications using the same postgres DB. A laravel application and a rails application. The current data in the DB is in Y-m-d H:i:s format but whenever rails adds a records, the format is Y-m-d H:i:s.u which includes milliseconds.

This causes the following error on the laravel side if the created_at date is ever references in laravel InvalidArgumentException Trailing data...

Laravel models can mutate their date format, so I can include make it conform to Y-m-d H:i:s.u then I would need to update all the records to have the miliseconds in the created_at date ( and any other timestamps). Unfortunatly when I specify the format to be Y-m-d H:i:s for the laravel model, it will not ignore the decimals. This is why I'm now looking for ways to default rails to save in the Y-m-d H:i:s format, instead of including the miliseconds. Then both the Rails and Laravel applications would be using the same format and there wouldn't be any conflict.

I know I can go into the DB and change the column type to timestamp(0) which truncates the decimals, but I would perfer to change the format that the frameworks are saving, rather then change what the DB will accept.

Both Y-m-d H:i:s and Y-m-d H:i:s.u are valid timestamps... If I could get Rails to use the Y-m-d H:i:s format or have Laravel ignore the .u when looking at timestamps to prevent the trailing data error, I would be in the clear.

Is there a way to change the default format that rails saves timestamps to the DB?

Is a way to have Larave Carbon ignore the decimal portion of a timestamp in the Y-m-d H:i:s.u format?

Thanks

Answer 1

Solution in the Rails side

It seems ActiveRecord used in Rails (5.2) automatically adds decimal seconds down to 1 msec in saving created_at and updated_at or any other Timestamp columns in the DB that accept sub-seconds, as defined in the file active_record/connection_adapters/abstract/quoting.rb

A work around is this. Add this line at a top level in any of the files which would be always read by Rails when accessing a model (such as, ApplicationRecord model file).

Time::DATE_FORMATS[:db] = '%Y-%m-%d %H:%M:%S.000000000'

module ActiveRecord::ConnectionAdapters::Quoting
  alias_method :quoted_date_orig, :quoted_date if ! self.method_defined?(:quoted_date_orig)

  def quoted_date(*rest, **kwd)
    quoted = quoted_date_orig(*rest, **kwd)
    quoted.sub(/(\.\d*)\.\d{6}$/, '\1')
  end
end

You can confirm it from Rails console, after creating a new record,

MyModel.last.created_at.nsec  # => 0

or simply access the DB directly to see it.

Warning

This change affects not only created_at and updated_at but also all the other timestamp columns in the DB. I think you can still save a value to msec (or nsec) precision to such a column by setting a String as opposed to a Time instance to your Model instance like my_model.col_msec_desired = "2018-01-02 03:04:05.678"; then Time::DATE_FORMATS[:db] would not be referenced in saving the record.

Potential solution in the Laravel side

It may be tricky at the time of writing (2018-10-18), but a work seems to be in progress, according to a very recent Laracast post by cmbertsch01

(Note: a major update made a day after from the original post, following the comment.)

Answer 2

In Laravel/PHP, you can convert DateTime with millisecond to without millisecond using below function:

$date = date('Y-m-d H:i:s.u');
echo date('Y-m-d H:i:s', strtotime($date));

In Rails, you can update your time object before sending it to the database, as mentioned below:

time.change(:usec => 0)

Answer 3

you can set Time::DATE_FORMATS[:db] in application.rb

Time::DATE_FORMATS[:db] = "Y-m-d H:i:s.u"

looks more at strftime format