1

I have a column _action_date which is bigint type.

The example data stored in it is:

1,530,402,820,197,192

I want to convert the date into timestamp without timezone format.

I tried to_timestamp() method but it is failing for bigint. I then divided it by 1000000 to eliminate the milliseconds part. But I am getting wrong date then.

Please help me in figuring out the issue.

I converted it using online epoch convertor and it is giving me this result.

GMT: Saturday, 30 June 2018 23:53:40
Your time zone: Sunday, 1 July 2018 05:23:40 GMT+05:30

I want the same result.

I am using Amazon Redshift

Bhawan
  • 2,441
  • 3
  • 22
  • 47
  • TO_CHAR(TO_TIMESTAMP(bigint_field / 1000), 'DD/MM/YYYY HH24:MI:SS') please try this. got from https://stackoverflow.com/questions/5485502/how-to-format-bigint-field-into-a-date-in-postgresql – Vishnu VaNnErI Sep 18 '18 at 06:13
  • @VishnuVaNnErI am getting this error after I run your query: Error running query: function to_timestamp(bigint) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. – Bhawan Sep 18 '18 at 06:17
  • You need to cast it, function is `to_timestamp(double precision)` – Łukasz Kamiński Sep 18 '18 at 06:22
  • `1,530,402,820,197,192` is not a valid "bigint" to begin with –  Sep 18 '18 at 07:39
  • PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.3906 – Bhawan Sep 18 '18 at 07:44
  • So, amazon redshift has no support for higher versions of postgres ? – Bhawan Sep 18 '18 at 08:30
  • 1
    Amazon does not "support" Postgres. Amazon took the code of Postgres 8.0 and continued development independently from the "real" Postgres code and never bothered to integrate enhancements from the core Postgres development back into their product. Those are two different products and it's extremely annoying that they still show the version as "PostgreSQL" (even the JDBC driver pretends that it's a Postgres database) –  Sep 18 '18 at 08:33
  • Just to add though, there are additions in Redshift on top of the base Postgres version 8. In fact, Redshift is based on Paraccel (https://en.wikipedia.org/wiki/ParAccel) which was based on postgres inititally. There are many other MPP datbases that also took this route e.g. IBM Netezza https://en.wikipedia.org/wiki/Netezza which was based on pg 7.2. I also wish they would break that link on the version as it confuses people pretty much every day on SO. – Jon Scott Sep 18 '18 at 08:48
  • Thanks for the clarification. It really helped. – Bhawan Sep 18 '18 at 10:18

3 Answers3

3

Using the special time epoch with some casting works well for me here:

SELECT TIMESTAMP WITHOUT TIME ZONE 'epoch' + (1530402820197192::bigint::float / 1000000) * INTERVAL '1 second';

Of course, replacing the 1530402820197192::bigint with your column will also help.

1

try: select to_timestamp(big_int_field::text, 'YYYYMMDDHH24MISS')

kaneki
  • 11
  • 3
  • Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – Tyler2P Dec 03 '20 at 13:06
0

Why don't you try the following

SELECT
tick.value
 , CAST((tick.value - 599266080000000000) / 864000000000 AS datetime) as DateTimeCalc1
FROM (values 
  (convert(bigint,1,530,402,820,197,192)) 
) AS tick(value);

Or you can use the following

select dateadd(s, convert(bigint, 1,530,402,820,197,192) / 1000, convert(datetime, '1-1-1970 00:00:00'))

Divide by 1000 to just reduce the bigint for for sql operation facilation

Debabrata
  • 124
  • 1
  • 10