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
Add a comment
|
4 Answers
TO_CHAR(TO_TIMESTAMP(bigint_field / 1000), 'DD/MM/YYYY HH24:MI:SS')
5 Comments
Quassnoi
@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?
Stephan
[BIGINT] - 1301486917594 roughly equals to 2011-03-30 14:08:37,594
Quassnoi
@Stephan: seems you are storing the number of milliseconds since
1970. See the post update.
Aditya Sawant
I am new to postgres. May I know why we need to divide bigint by 1000?
Quassnoi
@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.This is what worked for me
to_timestamp( bigint_field/1000)::date
1 Comment
Paul Pritchard
This solution does half the job - by providing the date. But Stephan clearly wants both the date and time to be output.
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"
Comments
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;