75

I have a table with a field of type bigint. This field store a timestamp. I want to date format the field like this :

to_char( bigint_field,'DD/MM/YYYY HH24:MI:SS')

I get the following error :

ERROR: multiple decimal points
État SQL :42601
Stephan
  • 41,764
  • 65
  • 238
  • 329

4 Answers4

134
TO_CHAR(TO_TIMESTAMP(bigint_field / 1000), 'DD/MM/YYYY HH24:MI:SS')
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • @Stephan: either that, or, more probably, what you are storing is a not `UNIX` timestamp. Could you please post a value of `bigint_field` and the date you think it should correspond to? – Quassnoi Mar 30 '11 at 12:04
  • 1
    [BIGINT] - 1301486917594 roughly equals to 2011-03-30 14:08:37,594 – Stephan Mar 30 '11 at 12:10
  • 4
    @Stephan: seems you are storing the number of milliseconds since `1970`. See the post update. – Quassnoi Mar 30 '11 at 12:12
  • I am new to postgres. May I know why we need to divide bigint by 1000? – Aditya Sawant Jun 16 '23 at 20:41
  • @AdityaSawant: read the comment thread. `TO_TIMESTAMP(BIGINT)` converts the Unix timestamp (number of seconds since 1970) to a timestamp. The op stores the number of milliseconds in his table. – Quassnoi Jun 16 '23 at 23:19
14

This is what worked for me

to_timestamp( bigint_field/1000)::date
Rene Enriquez
  • 1,418
  • 1
  • 13
  • 33
6

This depends on what the bigint value represents - offset of epoch time, or not.

select to_timestamp(20120822193532::text, 'YYYYMMDDHH24MISS')

returns

"2012-08-22 19:35:32+00"

j0k
  • 22,600
  • 28
  • 79
  • 90
Jordan K
  • 61
  • 1
  • 1
3

I did it like this:

to_timestamp(to_char(20120822193532, '9999-99-99 99:99:99'),'YYYY-MM-DD HH24:MI:SS')

the result looks like this:

2012-08-22 19:35:32

you also can use this in you select statemant, just exchange the number with your database colunm.

Step by Step Explanation:

to_char(20120822193532, '9999-99-99 99:99:99')

This will create a string like this:

"2012-08-22 19:35:32"

now we can easiely convert this into a timestamp:

to_timestamp('2012-08-22 19:35:32','YYYY-MM-DD HH24:MI:SS')

Result will look the same as before, but it's now a timestamp.

Also, if you use this for a command like 

CREATE TABLE table2 AS SELECT to_timestamp(to_char(tb1.date, '9999-99-99 99:99:99'),'YYYY-MM-DD HH24:MI:SS') AS realDate FROM table1 AS tb1;

you might end up with timstamptz (timestamp with time zone) instead of timestamp (timestamp without time zone). You can change it like this:

ALTER TABLE table2 ALTER realDate SET DATA TYPE timestamp USING realDate;
Th3NetForc3
  • 51
  • 3