<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=iso-8859-1"><meta name=Generator content="Microsoft Word 12 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Webdings;
panose-1:5 3 1 2 1 5 9 6 7 3;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EstiloCorreo17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 3.0cm 70.85pt 3.0cm;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-GB link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span lang=CA>Hi all,<o:p></o:p></span></p><p class=MsoNormal><span lang=CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=CA>I’m trying to create a MySQL select query to retrieve the publishing jobs’ time (Start time, finish time, queue time) and convert them to an understandable format (DateTime format). The problem is that OpenCms stores the dates as a UNIX timestamp albeit with three extra digits at the end of it.<o:p></o:p></span></p><p class=MsoNormal><span lang=CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=CA>When I’m converting these dates using web tools, I’m getting a wrong date when entering the OpenCms date (with the three extra digits). Conversely, when cutting off the three last digits, I’m getting the right date.<o:p></o:p></span></p><p class=MsoNormal><span lang=CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=CA>I want to achieve the same using MySQL query sintax, but all I’m getting is blank values or nulls. The troubling part is highlighted in yellow. None of the two date conversions I’m using is working at the moment.<o:p></o:p></span></p><p class=MsoNormal><span lang=CA><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New";color:blue'>select</span><span style='font-size:10.0pt;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal style='text-indent:36.0pt'><span style='font-size:10.0pt;font-family:"Courier New";color:black'>pj.HISTORY_ID </span><span style='font-size:10.0pt;font-family:"Courier New";color:blue'>as</span><span style='font-size:10.0pt;font-family:"Courier New";color:black'> ID_PUBLICACIO, <o:p></o:p></span></p><p class=MsoNormal style='margin-left:36.0pt'><span style='font-size:10.0pt;font-family:"Courier New";color:black'>p.PROJECT_ID </span><span style='font-size:10.0pt;font-family:"Courier New";color:blue'>as</span><span style='font-size:10.0pt;font-family:"Courier New";color:black'> ID_PROJECTE, <o:p></o:p></span></p><p class=MsoNormal style='margin-left:36.0pt'><span style='font-size:10.0pt;font-family:"Courier New";color:black'>p.PROJECT_NAME </span><span style='font-size:10.0pt;font-family:"Courier New";color:blue'>as</span><span style='font-size:10.0pt;font-family:"Courier New";color:black'> NOM_PROJECTE, <o:p></o:p></span></p><p class=MsoNormal style='margin-left:36.0pt'><span style='font-size:10.0pt;font-family:"Courier New";color:black'>p.PROJECT_DESCRIPTION </span><span style='font-size:10.0pt;font-family:"Courier New";color:blue'>as</span><span style='font-size:10.0pt;font-family:"Courier New";color:black'> DESC_PROJECTE, <o:p></o:p></span></p><p class=MsoNormal style='margin-left:36.0pt'><span style='font-size:10.0pt;font-family:"Courier New";color:black'>u.USER_ID </span><span style='font-size:10.0pt;font-family:"Courier New";color:blue'>as</span><span style='font-size:10.0pt;font-family:"Courier New";color:black'> ID_USUARI, <o:p></o:p></span></p><p class=MsoNormal style='margin-left:36.0pt'><span style='font-size:10.0pt;font-family:"Courier New";color:black'>u.USER_NAME </span><span style='font-size:10.0pt;font-family:"Courier New";color:blue'>as</span><span style='font-size:10.0pt;font-family:"Courier New";color:black'> NOM_USUARI, <o:p></o:p></span></p><p class=MsoNormal style='margin-left:36.0pt'><span style='font-size:10.0pt;font-family:"Courier New";color:black'>pj.RESOURCE_COUNT </span><span style='font-size:10.0pt;font-family:"Courier New";color:blue'>as</span><span style='font-size:10.0pt;font-family:"Courier New";color:black'> NUM_RECURSOS, <o:p></o:p></span></p><p class=MsoNormal style='margin-left:36.0pt'><span style='font-size:10.0pt;font-family:"Courier New";color:fuchsia;background:yellow;mso-highlight:yellow'>date_format</span><span style='font-size:10.0pt;font-family:"Courier New";color:black;background:yellow;mso-highlight:yellow'>(pj.ENQUEUE_TIME,</span><span style='font-size:10.0pt;font-family:"Courier New";color:red;background:yellow;mso-highlight:yellow'>'%d/%m/%Y %h:%m:%s:%f'</span><span style='font-size:10.0pt;font-family:"Courier New";color:black;background:yellow;mso-highlight:yellow'>) </span><span style='font-size:10.0pt;font-family:"Courier New";color:blue;background:yellow;mso-highlight:yellow'>as</span><span style='font-size:10.0pt;font-family:"Courier New";color:black;background:yellow;mso-highlight:yellow'> TEMPS_ENCUAT, <o:p></o:p></span></p><p class=MsoNormal style='margin-left:36.0pt'><span style='font-size:10.0pt;font-family:"Courier New";color:fuchsia;background:yellow;mso-highlight:yellow'>str_to_date</span><span style='font-size:10.0pt;font-family:"Courier New";color:black;background:yellow;mso-highlight:yellow'>(</span><span style='font-size:10.0pt;font-family:"Courier New";color:fuchsia;background:yellow;mso-highlight:yellow'>substring</span><span style='font-size:10.0pt;font-family:"Courier New";color:black;background:yellow;mso-highlight:yellow'>(pj.START_TIME,</span><b><span style='font-size:10.0pt;font-family:"Courier New";color:maroon;background:yellow;mso-highlight:yellow'>1</span></b><span style='font-size:10.0pt;font-family:"Courier New";color:black;background:yellow;mso-highlight:yellow'>,</span><b><span style='font-size:10.0pt;font-family:"Courier New";color:maroon;background:yellow;mso-highlight:yellow'>11</span></b><span style='font-size:10.0pt;font-family:"Courier New";color:black;background:yellow;mso-highlight:yellow'>), </span><span style='font-size:10.0pt;font-family:"Courier New";color:red;background:yellow;mso-highlight:yellow'>'%d/%m/%Y %h:%m:%s:%f'</span><span style='font-size:10.0pt;font-family:"Courier New";color:black;background:yellow;mso-highlight:yellow'>) </span><span style='font-size:10.0pt;font-family:"Courier New";color:blue;background:yellow;mso-highlight:yellow'>as</span><span style='font-size:10.0pt;font-family:"Courier New";color:black;background:yellow;mso-highlight:yellow'> TEMPS_INICI, <o:p></o:p></span></p><p class=MsoNormal style='margin-left:36.0pt'><span style='font-size:10.0pt;font-family:"Courier New";color:black;background:yellow;mso-highlight:yellow'>pj.FINISH_TIME </span><span style='font-size:10.0pt;font-family:"Courier New";color:blue;background:yellow;mso-highlight:yellow'>as</span><span style='font-size:10.0pt;font-family:"Courier New";color:black;background:yellow;mso-highlight:yellow'> TEMPS_FINAL</span><span style='font-size:10.0pt;font-family:"Courier New";color:black'> <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New";color:blue'>from</span><span style='font-size:10.0pt;font-family:"Courier New";color:black'> cms_publish_jobs pj, <o:p></o:p></span></p><p class=MsoNormal style='text-indent:36.0pt'><span style='font-size:10.0pt;font-family:"Courier New";color:black'>cms_projects p, <o:p></o:p></span></p><p class=MsoNormal style='text-indent:36.0pt'><span style='font-size:10.0pt;font-family:"Courier New";color:black'>cms_users u <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Courier New";color:blue'>order</span><span style='font-size:10.0pt;font-family:"Courier New";color:black'> </span><span style='font-size:10.0pt;font-family:"Courier New";color:blue'>by</span><span style='font-size:10.0pt;font-family:"Courier New";color:black'> FINISH_TIME </span><span style='font-size:10.0pt;font-family:"Courier New";color:blue'>desc</span><span style='font-size:10.0pt;font-family:"Courier New";color:gray'>;</span><span lang=CA><o:p></o:p></span></p><p class=MsoNormal><span lang=CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=CA>I would very much appreciate if someone shed some light upon this mistery,<o:p></o:p></span></p><p class=MsoNormal><span lang=CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=CA>Thanks in advance,<o:p></o:p></span></p><p class=MsoNormal><span lang=CA><o:p> </o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:5.95pt'><span lang=ES style='font-size:10.0pt;color:#1F497D'><a href="http://www.linkedin.com/in/alexrodba"><b><span style='color:#1F497D;text-decoration:none'>Àlex Rodríguez Bacardit</span></b></a></span><span lang=ES style='font-size:10.0pt;color:navy'><br></span><span lang=ES style='font-size:10.0pt;color:#1F497D'>Consultor</span><span lang=ES style='font-size:10.0pt;color:navy'> e-Business<br><a href="mailto:alex.rodriguez@vass.es"><span style='color:blue'>alex.rodriguez@vass.es</span></a></span><span lang=ES style='font-size:10.0pt'><o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:auto;margin-bottom:5.95pt'><b><span lang=ES style='font-size:10.0pt;color:gray'>VASS Consultoría de Sistemas</span></b><b><span lang=ES style='font-size:10.0pt;color:#1F497D'><br></span></b><span lang=ES style='font-size:10.0pt;color:gray'>Tel: +34 931 124 794</span><span lang=ES style='font-size:10.0pt;color:gray'>1</span><span lang=ES style='font-size:10.0pt;color:gray'><br>Fax: +34 933 184 291<br>Avinguda Diagonal 210, 4a planta<br>08018 Barcelona<br><a href="http://www.vass.es/"><span style='color:black'>www.vass.es</span></a><o:p></o:p></span></p><p class=MsoNormal style='mso-line-height-alt:6.0pt'><span lang=ES style='font-size:7.5pt;font-family:"Verdana","sans-serif";color:#999999'>Este correo electrónico así como los documentos adjuntos contienen información confidencial y reservada. Si ha recibido este mensaje por error siga las instrucciones contenidas en nuestro </span><span lang=ES><a href="http://vass.es/es/aviso-legal"><span style='font-size:7.5pt;font-family:"Verdana","sans-serif";color:blue'>Aviso Legal</span></a></span><span lang=ES style='font-size:7.5pt;font-family:"Verdana","sans-serif";color:#999999'>. Si no deseara recibir más comunicaciones por correo electrónico comuníquelo en la siguiente dirección: </span><span lang=ES><a href="mailto:general@vass.es"><span style='font-size:7.5pt;font-family:"Verdana","sans-serif";color:blue'>general@vass.es</span></a></span><span lang=ES style='font-size:7.5pt;font-family:"Verdana","sans-serif";color:#999999'> <o:p></o:p></span></p><p class=MsoNormal><span lang=ES-TRAD style='font-size:9.0pt;font-family:Webdings;color:green'>P</span><span lang=ES-TRAD style='font-size:7.5pt;font-family:"Tahoma","sans-serif";color:blue'> </span><span lang=ES-TRAD style='font-size:7.0pt;font-family:"Tahoma","sans-serif";color:olive'>Antes de imprimir este mensaje, asegúrese de que es necesario. El medio ambiente está en nuestra mano.</span><span lang=ES-TRAD style='font-size:7.5pt;font-family:"Tahoma","sans-serif";color:olive'><o:p></o:p></span></p><p class=MsoNormal><span lang=CA><o:p> </o:p></span></p></div></body></html>