Timestamp can't be saved - postgresql

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?

If you get no error message, how are you determining that it "can't be saved"? Have you checked the PostgreSQL error logs? Enabled query logging and looked at the logs? — Craig Ringer, Nov 11 '12 at 11:53
I could'nt find something related to this issue in `var/log/postgresql/postgresql-9.1-main.log` and `/var/log/syslog`. In general can we store timestamp in a DateTime field? — newbi, Nov 11 '12 at 12:02

score 3 · Answer 1 · 2012-11-11T16:23:23.763

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

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

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

More about the handling of timestamps in Postgres in this related answer.

I would save the value as timestamp with time zone as is (no multiplication). You can always extract ms out of it 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 - install ntp. 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. 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, 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.

So this would be all you need:

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 update it ON UPDATE, add a TRIGGER like this:

Trigger function:

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

Trigger:

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

Now, everything works automatically.
If that's not what you are after, @slivu's answer seems to cover the Ruby side just nicely.

As you see in my post I am using postgresql via DataMapper, when I set the time as Timestamp, then rails complain about the type? — newbi, Nov 11 '12 at 15:27

score 0 · Answer 3 · edited Nov 11 '12 at 16:37

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.

Timestamp can't be saved - postgresql

3 Answers3

See live demo here

Just save "now"

Linked