How to insert a value into SQLAlchemy TIMESTAMP column

Question

I am trying to set value for timestamp column using SQLAlchemy, but I experience following error:

column "timestamp" is of type timestamp without time zone but expression is of type numeric

My table looks as folows:

class SomeTable(db.Model):
    timestamp = Column(TIMESTAMP)

Insertion try looks like this:

SomeTable(timestamp=time.time())

When I use datetime.now() instead of time.time() the record is inserted into the table but when I select it from the database it has following format:

2019-04-02 11:44:24.801046

So it looks like TIMESTAMP field does not store timestamp format.

Is it regular postgres behaviour? Or am I missing something?

Milenko Jevremovic · Accepted Answer · 2019-04-02 12:47:36Z

4

I think that is good, because next is correct:

Column('timestamp', TIMESTAMP(timezone=False), nullable=False, default=datetime.now())

so by default you have datetime.now() there, it is about presentation

datetime.now() will give you '2019-04-02 14:21:33.715782

datetime.now().isoformat() will give you '2019-04-02T14:31:09.071147'

Check it with: http://www.sqlfiddle.com/#!15/d0c6a/8

If you uncomment third insert you will get exact same exception as yours

answered Apr 2, 2019 at 12:47

Milenko Jevremovic

1,0569 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Charles L. Over a year ago

Even better is using the sqlalchmey.func.now variable. It's better to use the database server's time.

giuppep Over a year ago

I think that having default=datetime.now() will cause all your entries to have the same timestamp as that global datetime.now() will only be computed when the app is loaded. You probably need datetime.now instead. Or use the db function as Charles has suggested.

Collectives™ on Stack Overflow

How to insert a value into SQLAlchemy TIMESTAMP column

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related