Postgres - convert dateString to int8

Question

How can i convert this string to a int8 in PostgreSQL 12?

2021-10-18T17:45:22Z

i tried:

TO_TIMESTAMP('2021-10-18T17:45:22Z'::text,'YYYY-MM-DD hh:mm:ss ')::int8
to_char('2021-10-18T17:45:22Z', 'YYYY-MM-DD hh:mm:ss ')::integer

and a few variations of these but without any success.

The Input string cannot change because it is passed this way by an external software.

What's that integer supposed to mean? Dates in all databases except SQLite are separate binary types, not strings. Almost all languages have date types too, so there's little reason to convert a date to something else — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 3, 2021 at 12:13
As for 2021-10-18T17:45:22Z that's not just a date, it's a date with an offset. This represents different times in each timezone. Removing the offset loses important information — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 3, 2021 at 12:15
@PanagiotisKanavos The Z at the end stands for Zulu time zone, i.e. UTC. So it can be safely omitted given that timestamps are stored in UTC by default — Patrick
– Patrick, Commented Nov 3, 2021 at 12:21
@Patrick I know what this means and how those defaults aren't really. Check Falsehoods programmers believe about time — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 3, 2021 at 12:24

Tim Biegeleisen · Accepted Answer · 2021-11-03 12:11:35Z

3

You didn't tell us what the integer value is supposed to represent. One general approach here would be to work with UNIX timestamps. For example:

SELECT EXTRACT(epoch FROM '2021-10-18 17:45:22'::timestamp);  -- 1634579122

The value 1634579122 is the number of seconds which has elapsed since January 1, 1970.

answered Nov 3, 2021 at 12:11

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

3 Comments

sharkyenergy Over a year ago

You are right, i fogot to mention that. unix timestamp is exactly what I need. thank you

Panagiotis Kanavos Over a year ago

@sharkyenergy why do you need it? Dates are built-in types in almost all databases and languages. Using the correct type is actually a lot safer and more portable than something ambiguous like a Unix timestamp (what timezone? what precision?)

sharkyenergy Over a year ago

@PanagiotisKanavos the database i got (not managed by me) has the date saved as int8...

Collectives™ on Stack Overflow

Postgres - convert dateString to int8

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related