Date: Wed, 21 Jan 2026 16:06:58 +0000 (UTC)
From: Thomas Carroll <tomfecarroll@yahoo.com>
To: Erik Wienhold <ewie@ewie.name>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: SASIKUMAR Devaraj <sashikumard@yahoo.com>,
	"pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>
Message-ID: <1473772846.1590447.1769011618201@mail.yahoo.com>
In-Reply-To: <2193943.1769007995@sss.pgh.pa.us>
References: <1903154046.1708912.1769003607609.ref@mail.yahoo.com>
 <1903154046.1708912.1769003607609@mail.yahoo.com>
 <fd4170a9-c849-4226-b6d0-35560b6a805b@ewie.name>
 <2193943.1769007995@sss.pgh.pa.us>
Subject: Re: Sybase to postgres Timestamp column
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_1590446_1250658125.1769011618200"
Content-Length: 5573
Archived-At: 
 <https://www.postgresql.org/message-id/1473772846.1590447.1769011618201%40mail.yahoo.com>
Precedence: bulk

------=_Part_1590446_1250658125.1769011618200
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

 A couple of things to look out for in terms of dates that I have come acro=
ss.=C2=A0 First, date formats:
Aug 20 2004=C2=A0 4:23:07.786PM -- Postgres
Aug 20 2004=C2=A0 4:23:07:786PM -- Sybase
Note the separator difference between seconds and milliseconds (dot vs. col=
on).
Second: date math is different for Sybase vs. Postgres.=C2=A0 A difference =
in days in Sybase is computed by counting date thresholds crossed.=C2=A0 If=
 I recall, Postgres counts 24 hour periods crossed.
Hope this helps.
Tom Carroll
    On Wednesday, January 21, 2026 at 10:07:00 AM EST, Tom Lane <tgl@sss.pg=
h.pa.us> wrote: =20
=20
 Erik Wienhold <ewie@ewie.name> writes:
> On 2026-01-21 14:53 +0100, SASIKUMAR Devaraj wrote:
>> When we are migrating from sybase to postgres, we are facing issues
>> with Timestamp field format and not matching.
>> Sybase format: Jan 21 2026 5:35 PM

> I'm not familiar with Sybase.=C2=A0 What issues are you facing?
> But Postgres should accept this timestamp format without issue:

Yeah, I don't see a difficulty on the input side, so I'm guessing
the OP is wishing Postgres would output timestamps in that format.
Sorry, none of the built-in datestyle settings quite match that.
It's easy to replicate pretty closely using to_char():

postgres=3D# select to_char('Jan 21 2026 5:35 PM'::timestamp, 'Mon DD YYYY =
HH:MI PM');
=C2=A0 =C2=A0 =C2=A0 to_char=C2=A0 =C2=A0 =C2=A0 =C2=A0=20
----------------------
 Jan 21 2026 05:35 PM
(1 row)

But wrapping all your timestamp output columns in to_char() might be
more trouble than fixing the client-side code to accept ISO format.

=C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 regards, tom lane


 =20
------=_Part_1590446_1250658125.1769011618200
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html><head></head><body><div class=3D"ydpa9d424c9yahoo-style-wrap" style=
=3D"font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:16px=
;"><div></div>
        <div dir=3D"ltr" data-setdir=3D"false">A couple of things to look o=
ut for in terms of dates that I have come across.&nbsp; First, date formats=
:</div><div dir=3D"ltr" data-setdir=3D"false"><br></div><div dir=3D"ltr" da=
ta-setdir=3D"false"><div>Aug 20 2004&nbsp; 4:23:07.786PM -- Postgres<br>Aug=
 20 2004&nbsp; 4:23:07:786PM -- Sybase</div><br></div><div dir=3D"ltr" data=
-setdir=3D"false">Note the separator difference between seconds and millise=
conds (dot vs. colon).</div><div dir=3D"ltr" data-setdir=3D"false"><br></di=
v><div dir=3D"ltr" data-setdir=3D"false">Second: date math is different for=
 Sybase vs. Postgres.&nbsp; A difference in days in Sybase is computed by c=
ounting date thresholds crossed.&nbsp; If I recall, Postgres counts 24 hour=
 periods crossed.</div><div dir=3D"ltr" data-setdir=3D"false"><br></div><di=
v dir=3D"ltr" data-setdir=3D"false">Hope this helps.</div><div dir=3D"ltr" =
data-setdir=3D"false"><br></div><div dir=3D"ltr" data-setdir=3D"false">Tom =
Carroll</div><div><br></div>
       =20
        </div><div id=3D"yahoo_quoted_9334425311" class=3D"yahoo_quoted">
            <div style=3D"font-family:'Helvetica Neue', Helvetica, Arial, s=
ans-serif;font-size:13px;color:#26282a;">
               =20
                <div>
                        On Wednesday, January 21, 2026 at 10:07:00 AM EST, =
Tom Lane &lt;tgl@sss.pgh.pa.us&gt; wrote:
                    </div>
                    <div><br></div>
                    <div><br></div>
               =20
               =20
                <div><div dir=3D"ltr">Erik Wienhold &lt;<a shape=3D"rect" y=
mailto=3D"mailto:ewie@ewie.name" href=3D"mailto:ewie@ewie.name">ewie@ewie.n=
ame</a>&gt; writes:<br clear=3D"none">&gt; On 2026-01-21 14:53 +0100, SASIK=
UMAR Devaraj wrote:<br clear=3D"none">&gt;&gt; When we are migrating from s=
ybase to postgres, we are facing issues<br clear=3D"none">&gt;&gt; with Tim=
estamp field format and not matching.<br clear=3D"none">&gt;&gt; Sybase for=
mat: Jan 21 2026 5:35 PM<br clear=3D"none"><br clear=3D"none">&gt; I'm not =
familiar with Sybase.&nbsp; What issues are you facing?<br clear=3D"none">&=
gt; But Postgres should accept this timestamp format without issue:<br clea=
r=3D"none"><br clear=3D"none">Yeah, I don't see a difficulty on the input s=
ide, so I'm guessing<br clear=3D"none">the OP is wishing Postgres would out=
put timestamps in that format.<br clear=3D"none">Sorry, none of the built-i=
n datestyle settings quite match that.<br clear=3D"none">It's easy to repli=
cate pretty closely using to_char():<br clear=3D"none"><br clear=3D"none">p=
ostgres=3D# select to_char('Jan 21 2026 5:35 PM'::timestamp, 'Mon DD YYYY H=
H:MI PM');<br clear=3D"none">&nbsp; &nbsp; &nbsp;  to_char&nbsp; &nbsp; &nb=
sp; &nbsp; <br clear=3D"none">----------------------<br clear=3D"none"> Jan=
 21 2026 05:35 PM<div class=3D"yqt5476610994" id=3D"yqtfd82833"><br clear=
=3D"none">(1 row)</div><br clear=3D"none"><br clear=3D"none">But wrapping a=
ll your timestamp output columns in to_char() might be<br clear=3D"none">mo=
re trouble than fixing the client-side code to accept ISO format.<br clear=
=3D"none"><br clear=3D"none">&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&n=
bsp;&nbsp; regards, tom lane<div class=3D"yqt5476610994" id=3D"yqtfd90819">=
<br clear=3D"none"><br clear=3D"none"><br clear=3D"none"></div></div></div>
            </div>
        </div></body></html>
------=_Part_1590446_1250658125.1769011618200--