From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: Raj kumar <rajkumar820999@gmail.com>,
        Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Conversion of a column from Integer format type to 'timestamp
 with time zone'
In-reply-to: 
 <CAKFQuwZYWsBb49OZsS=H-SDhCu2_tSFiypiX7mfbQTyAZMobWg@mail.gmail.com>
References: 
 <CACxU--WR-nWB+gVGoTfrvYNb43HxzQNATW7HhSzkw291Qvifeg@mail.gmail.com>
 <CAKFQuwZYWsBb49OZsS=H-SDhCu2_tSFiypiX7mfbQTyAZMobWg@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Thu, 26 Jan 2023 13:02:00 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3165930.1674763810.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 26 Jan 2023 15:10:10 -0500
Message-ID: <3165931.1674763810@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3165931.1674763810%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Jan 26, 2023 at 12:45 PM Raj kumar <rajkumar820999@gmail.com> wr=
ote:
>> Hi, there is a column 'created' in the 'customers' table which is actua=
lly
>> 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' fo=
rmat?
>> =

>> 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 th=
at
> 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:

=3D# 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