Convert Timestamp without timezone to integer Postgres

Question

I've inherited a database with a quirky schema. Without boring of the details I have an odd use case.

The table contains a filed completed_at that is a timestamp without timezone. What I'm trying to do is order the results by the timestamp where the timestamp is > X

The kicker is X is a numeric (or decimal) value. Trying to convert the timestamp to int is failing or even to string.

If you simply try completed_at > 0 you get

Does the integer represent "epoch" time, or is it an embedded timestamp like 201501010800 (meaning 2015-01-01 08:00)? — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Jan 26, 2015 at 18:52

Ulysse BN · Accepted Answer · 2018-11-14 14:24:24Z

45

The only answer I found was in this message: https://www.postgresql.org/message-id/3EE8B4ED.1060200%40pgsqldb.com

Basically, I guess, the answer is: select cast(extract(epoch from current_timestamp) as integer);

edited Nov 14, 2018 at 14:24

Ulysse BN

11.7k7 gold badges64 silver badges95 bronze badges

answered Aug 28, 2017 at 7:04

Stas Shafeev

7166 silver badges8 bronze badges

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

Comments

Raunak Chowdhury · Accepted Answer · 2020-05-18 16:39:12Z

7

I am using the below code to convert the current time to Bigint.

SELECT cast(to_char((current_timestamp)::TIMESTAMP,'yyyymmddhhmiss') as BigInt)

answered May 18, 2020 at 16:39

Raunak Chowdhury

2292 silver badges4 bronze badges

Comments

Michael Spagon · Accepted Answer · 2019-05-11 08:35:25Z

0

I had a similar problem: I wanted to extract the year as an Integer, so using the above I wrote CAST(EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS INTEGER)into my PostgreSQL database and then examined the constraint in PGADMIN 4 and it came up with the following alternative, which I found interesting:

date_part('year'::text, CURRENT_TIMESTAMP)::integer

answered May 11, 2019 at 8:35

Michael Spagon

1514 bronze badges

Comments

kayleighsdaddy · Accepted Answer · 2015-01-26 17:55:43Z

-13

I would use the following code if it is SQL. It works for me. Change the ASC to DESC if it is the wrong way. You should not need to convert anything.

ORDER BY `timestamp_column` ASC

answered Jan 26, 2015 at 17:55

kayleighsdaddy

6705 silver badges16 bronze badges

1 Comment

Frank Heikens Over a year ago

Backticks ` won't work, that's a MySQL-only thing. Don't use it, you never need it. If you really really want something around your database objects, use double quotes ", that's a standard in SQL and works in most databases. Including MySQL.

Collectives™ on Stack Overflow

Convert Timestamp without timezone to integer Postgres

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related