Timestamp can't be saved as epoch number

Question

I have the below code snippet:

class Foo    
  include DataMapper::Resource
  property :id, Serial
  property :timestamp, DateTime
 end

I just want to convert the current time to ms:

 class Time
   def to_ms
     (self.to_f * 1000.0).to_i
   end
 end
    
 def current_time
   time = Time.now
   return time.to_ms
  end

  time = current_time # => 1352633569151

But when I am going to save the Foo with above timestamp, then it can't be saved to the database and I'm not getting any error message.

foo = Foo.new
foo.timestamp = time
foo.save

Any idea?

Answer 1

are you using a correct format for your :datetime property?

should be like:

DateTime.now.to_s
=> "2012-11-11T14:04:02+02:00"

or a "native" DateTime object, without any conversions.

DataMapper will carry to convert it to according values based on adapter used.

also, to have exceptions raised when saving items:

DataMapper::Model.raise_on_save_failure = true 

that's a global setting, i.e. all models will raise exceptions.

to make only some model to behave like this:

YourModel.raise_on_save_failure = true

http://datamapper.org/docs/create_and_destroy.html

See "Raising an exception when save fails" chapter

btw, to see what's wrong with your item before saving it, use and item.valid? and item.errors

foo = Foo.new
foo.timestamp = time
if foo.valid?
  foo.save
else
  p foo.errors
end

I replicated your code and got following error:

@errors={:timestamp=>["Timestamp must be of type DateTime"]}

See live demo here

Answer 2

The PostgreSQL data types would be timestamp or timestamptz. But that contradicts what you are doing. You take the epoch value and multiply by 1000. You'd have to save that as bigint or some numeric type.

Basics:

• Ignoring time zones altogether in Rails and PostgreSQL

I would save the value as timestamptz as is. (No multiplication.) You can always extract ms if need should be.

If you need to translate the Unix epoch value back to a timestamp, use:

SELECT to_timestamp(1352633569.151);
--> timestamptz 2012-11-11 12:32:49.151+01

Just save "now"

If you actually want to save "now", i.e. the current point in time, then let Postgres do it for you. Just make sure the database server has a reliable local time. (Maybe install ntp on the server.) This is generally more reliable, accurate and simple.

Set the DEFAULT of the timestamp column to now() or CURRENT_TIMESTAMP.
If you want timestamp instead of timestamptz you can still use now(), which is translated to "local" time according to the servers timezone setting - effectively the same as LOCALTIMESTAMP. Or, to get the time for a given time zone:

now() AT ZIME ZONE 'Europe/Vienna'  -- your time zone here

Or, in your particular case, since you seem to want only three fractional digits: now()::timestamp(3), or CURRENT_TIMESTAMP(3), or CURRENT_TIMESTAMP(3) AT ZIME ZONE 'Europe/Vienna', or date_trunc('ms', LOCALTIMESTAMP). The latter truncates, while the other ones round. Typically, you want to truncate. See:

• Discard millisecond part from timestamp

Or, if you define the type of the column as timestamp(3), all timestamp values are coerced to the type and rounded to 3 fractional decimal digits automatically:

CREATE TABLE tbl (
   -- other columns
, ts_column timestamp(3) DEFAULT now()
);

The value is set automatically on INSERT, you don't even have to mention the column.
If you want to do it ON UPDATE, add a TRIGGER like:

Trigger function:

CREATE OR REPLACE FUNCTION trg_up_ts()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.ts_column := now();
   RETURN NEW;
END
$func$

Trigger:

CREATE TRIGGER log_up_ts
BEFORE UPDATE ON tbl
FOR EACH ROW EXECUTE FUNCTION trg_up_ts();

In Postgres 10 or older use the key word PROCEDURE instead of FUNCTION. See:

• Trigger uses a procedure or a function?

Now, everything works automatically.

Answer 3

I'm not familiar with PostgreSQL, but why are you assigning a Fixnum (time) to timestamp (which is a DateTime)? Your model must be failing to convert time to a proper DateTime value before generating the SQL.

Try foo.save!. I'm pretty sure you'll see an error, either reported from PostgreSQL, saying 1352633569151 is not a valid value for the table column, or your model will say it can't parse 1352633569151 to a valid DateTime.

foo.timestamp = Time.now or foo.timestamp = '2012-11-11 00:00:00' is something that'll work.