Thread: Timestamp in PG - 7.1 & 7.2
<div class="Section1"><p class="MsoNormal"><font color="blue" face="Arial" size="2"><span style="font-size: 10.0pt;font-family:Arial;color:blue">Hi,</span></font><p class="MsoNormal"><font color="blue" face="Times New Roman" size="3"><spanstyle="font-size:12.0pt;color:blue">I�ve observed </span></font><font color="blue"><span style="color:blue">t</span></font><fontcolor="blue"><span style="color:blue">his:</span></font><p class="MsoNormal"><fontcolor="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New";color:blue">Hereis ou</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue">pu</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue"> from Pos</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue">greSQL 7.1.2:</span></font><font color="blue"><span style="color: blue"></span></font><p class="MsoNormal"><font color="blue" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:blue"> </span></font><pclass="MsoNormal"><span class="GramE"><font color="blue" face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New";color:blue">t</span></font></span><spanclass="GramE"><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family: "Courier New";color:blue">rapdb</span></font></span><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew"; color:blue">=# selec</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue"> cas</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue">(da</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue">e</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue">ime(1057637700) as </span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue">imes</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue">amp);<br /> <span class="GramE">?column?</span> <br /> ---------------------------<br /> 2003-07-08</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">09:45:00</span></font><font color="blue" face="Courier New"size="2"><span style="font-size:10.0pt;font-family:"Courier New"; color:blue">+</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">05:30</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue"><br /> (1 row)</span></font><p class="MsoNormal"><font color="blue" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:blue"> </span></font><pclass="MsoNormal"><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New";color:blue">Here is ou</span></font><font color="blue" face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New";color:blue">t</span></font><font color="blue"face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New"; color:blue">pu</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue"> from Pos</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue">greSQL 7.2.4:</span></font><p class="MsoNormal"><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue"> </span></font><p class="MsoNormal"><span class="GramE"><fontcolor="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"Courier New";color:blue">t</span></font></span><spanclass="GramE"><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family: "Courier New";color:blue">rapdb</span></font></span><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew"; color:blue">=# selec</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue"> cas</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue">(da</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue">e</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue">ime(1057637700) as </span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue">imes</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue">amp);<br /> </span></font><span class="GramE"><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">t</span></font></span><span class="GramE"><font color="blue"face="Courier New" size="2"><span style="font-size: 10.0pt;font-family:"Courier New";color:blue">imes</span></font></span><span class="GramE"><font color="blue" face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New";color:blue">t</span></font></span><spanclass="GramE"><font color="blue" face="Courier New" size="2"><span style="font-size: 10.0pt;font-family:"Courier New";color:blue">amp</span></font></span><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family: "Courier New";color:blue"><br /> ---------------------------<br /> 2003-07-07 </span></font><font color="blue" face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New";color:blue">22:45:00</span></font><fontcolor="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew"; color:blue">+</span></font><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue">05:30</span></font><font color="blue" face="Courier New" size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; color:blue"><br /> (1 row)</span></font><p class="MsoNormal"><font color="blue" face="Courier New" size="2"><span style="font-size:10.0pt;font-family:"CourierNew";color:blue"> </span></font><p class="MsoNormal"><font color="blue" face="TimesNew Roman" size="3"><span style="font-size:12.0pt;color:blue">I�ve migra</span></font><font color="blue"><spanstyle="color:blue">t</span></font><font color="blue"><span style="color:blue">ed from PG - 7.1.2 </span></font><fontcolor="blue"><span style="color:blue">t</span></font><font color="blue"><span style="color:blue">o 7.2.4.</span></font><pclass="MsoNormal"><font color="blue" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:blue"> </span></font><pclass="MsoNormal"><font color="blue" face="Times New Roman" size="3"><spanstyle="font-size:12.0pt;color:blue">My </span></font><font color="blue"><span style="color:blue">t</span></font><fontcolor="blue"><span style="color:blue">imezone is se</span></font><font color="blue"><spanstyle="color:blue">t</span></font><font color="blue"><span style="color:blue"> as �IST� in bo</span></font><fontcolor="blue"><span style="color:blue">t</span></font><font color="blue"><span style="color:blue">h cases.</span></font><pclass="MsoNormal"><font color="blue" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:blue">Is</span></font><font color="blue"><span style="color:blue">t</span></font><font color="blue"><spanstyle="color:blue">his any version problem <span class="GramE">or<span style="mso-spacerun:yes">� </span>I�m</span>missing any envirnonmen</span></font><font color="blue"><span style="color:blue">t</span></font><font color="blue"><spanstyle="color:blue"> se</span></font><font color="blue"><span style="color:blue">t</span></font><font color="blue"><spanstyle="color:blue">t</span></font><font color="blue"><span style="color:blue">ing?</span></font><p class="MsoNormal"><fontcolor="blue" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:blue"> </span></font><pclass="MsoNormal"><font color="blue" face="Times New Roman" size="3"><spanstyle="font-size:12.0pt;color:blue">Help is apprecia</span></font><font color="blue"><span style="color:blue">t</span></font><fontcolor="blue"><span style="color:blue">ed�.</span></font><p class="MsoNormal"><fontcolor="blue" face="Times New Roman" size="3"><span style="font-size:12.0pt;color:blue"> </span></font><pclass="MsoNormal"><font color="blue" face="Times New Roman" size="3"><spanstyle="font-size:12.0pt;color:blue">Thanks,</span></font><p class="MsoNormal"><font color="blue" face="TimesNew Roman" size="3"><span style="font-size:12.0pt;color:blue">Anagha</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font></div>
"Anagha Joshi" <ajoshi@nulinkinc.com> writes:
> Here is output from PostgreSQL 7.2.4:
> =20
> trapdb=3D# select cast(datetime(1057637700) as timestamp);
> timestamp
> ---------------------------
> 2003-07-07 22:45:00+05:30
> (1 row)
> =20
> I've migrated from PG - 7.1.2 to 7.2.4.
Not sure why 7.2 is misbehaving here --- it looks to me like it's adding
instead of subtracting the timezone offset, but I don't see any mention
of having fixed such a bug in the CVS logs.
I'd suggest changing your code to abstime(n) instead of datetime(n) ---
you will have to do that when you update to 7.3 anyway, and it gets the
right answer on 7.2.
regards, tom lane