pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedConversion of a column from Integer format type to 'timestamp with time zone'
3+ messages / 3 participants
[nested] [flat]
* Conversion of a column from Integer format type to 'timestamp with time zone'
@ 2023-01-26 19:45 Raj kumar <rajkumar820999@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Raj kumar @ 2023-01-26 19:45 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>; +Cc: Raj kumar <rajkumar820999@gmail.com>
Hi, there is a column 'created' in the 'customers' table which is actually
supposed to be a 'timestamp with time zone' column but stored in this
Integer format. How do I typecast this to 'timestamp with time zone' format?
select created from customers limit 3;
created
---------------
1521521848681
1508995056368
1521559994299
(3 rows)
Thanks,
Raj Kumar Narendiran.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Conversion of a column from Integer format type to 'timestamp with time zone'
@ 2023-01-26 20:02 David G. Johnston <david.g.johnston@gmail.com>
parent: Raj kumar <rajkumar820999@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2023-01-26 20:02 UTC (permalink / raw)
To: Raj kumar <rajkumar820999@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Thu, Jan 26, 2023 at 12:45 PM Raj kumar <rajkumar820999@gmail.com> wrote:
> Hi, there is a column 'created' in the 'customers' table which is actually
> supposed to be a 'timestamp with time zone' column but stored in this
> Integer format. How do I typecast this to 'timestamp with time zone' format?
>
> select created from customers limit 3;
> created
> ---------------
> 1521521848681
> 1508995056368
> 1521559994299
> (3 rows)
>
>
We have no way of knowing what the corresponding point-in-time a 0 in that
column represents. Or, for that matter, what a 1 means.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Conversion of a column from Integer format type to 'timestamp with time zone'
@ 2023-01-26 20:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2023-01-26 20:10 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Raj kumar <rajkumar820999@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Jan 26, 2023 at 12:45 PM Raj kumar <rajkumar820999@gmail.com> wrote:
>> Hi, there is a column 'created' in the 'customers' table which is actually
>> supposed to be a 'timestamp with time zone' column but stored in this
>> Integer format. How do I typecast this to 'timestamp with time zone' format?
>>
>> select created from customers limit 3;
>> created
>> ---------------
>> 1521521848681
>> 1508995056368
>> 1521559994299
>> (3 rows)
> We have no way of knowing what the corresponding point-in-time a 0 in that
> column represents. Or, for that matter, what a 1 means.
A plausible guess is that those are Unix timestamps expressed in
milliseconds, in which case you could do this:
=# select to_timestamp(1521521848681 / 1000.0);
to_timestamp
----------------------------
2018-03-20 00:57:28.681-04
(1 row)
But really you need to find out, not guess.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-01-26 20:10 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-01-26 19:45 Conversion of a column from Integer format type to 'timestamp with time zone' Raj kumar <rajkumar820999@gmail.com>
2023-01-26 20:02 ` David G. Johnston <david.g.johnston@gmail.com>
2023-01-26 20:10 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox