1

I am creating a table in my PostGres db but the time stamp show as : 

2015-01-25T22:22:46+08:00

I would like to have it show as seconds instead of date .

This is what I have done : 

def upgrade():
    op.execute('''
    CREATE TABLE api_responses (
        id TEXT PRIMARY KEY,
        ts timestamp without time zone not null default (now() at time zone 'utc'),
        userid TEXT,
        response JSONB NOT NULL DEFAULT '{}',
        intent TEXT
       );
    ''')
    op.execute('CREATE INDEX ON api_responses (id);')
    op.execute('CREATE INDEX ON api_responses (ts DESC);')


def downgrade():
    op.execute('DROP TABLE api_responses')

I didn't find any options to transform now() and get it in seconds

I do not want to do it during the select/extract but automatically through the alembic versioning mecanism

Improve this question
5
  • 3
    Possible duplicate of How to convert a timestamp to an integer (Unix epoch) in Postgres Commented Aug 16, 2017 at 16:34
  • 1
    You're likely look for epoch. Search this page for epoch: postgresql.org/docs/current/static/functions-datetime.html Commented Aug 16, 2017 at 16:48
  • @ScottMarlowe it's not exactly the same because I want to avoid saving in the DB the timestamp as date/time. I have updated the code to show my point. I am using alembic versioning and I try to do it inside this file Commented Aug 16, 2017 at 23:43
  • Internally postgresql stores the timestamp as an offset from 2000-01-01 00:00:00 UTC. Commented Aug 17, 2017 at 0:28
  • @ScottMarlowe My bad, I wasn't clear. what I want is a kind of int. link provided seems doing what I want but I try to get it inside the py for alembic/postgres instead of the code who extract the data Commented Aug 17, 2017 at 0:31
Related questions
1
Querying for timestamp Postgresql
2
PostgreSQL time conversion/formatting from "24:00:00" to "00:00:00" in SELECT statement
3
Python format date string from postgresql db
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.