[opencms-dev] [MySQL] exporting publishing dates in custom format

[Àlex Rodríguez Bacardit]

Hi all,

 

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.

 

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.

 

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.

 

select 

pj.HISTORY_ID as ID_PUBLICACIO, 

p.PROJECT_ID as ID_PROJECTE, 

p.PROJECT_NAME as NOM_PROJECTE, 

p.PROJECT_DESCRIPTION as DESC_PROJECTE, 

u.USER_ID as ID_USUARI, 

u.USER_NAME as NOM_USUARI, 

pj.RESOURCE_COUNT as NUM_RECURSOS, 

date_format(pj.ENQUEUE_TIME,'%d/%m/%Y %h:%m:%s:%f') as TEMPS_ENCUAT, 

str_to_date(substring(pj.START_TIME,1,11), '%d/%m/%Y %h:%m:%s:%f') as
TEMPS_INICI,  

pj.FINISH_TIME as TEMPS_FINAL 

from cms_publish_jobs pj, 

cms_projects p, 

cms_users u 

order by FINISH_TIME desc;

 

I would very much appreciate if someone shed some light upon this mistery,

 

Thanks in advance,

 

 <http://www.linkedin.com/in/alexrodba> Àlex Rodríguez Bacardit
Consultor e-Business
 <mailto:alex.rodriguez at vass.es> alex.rodriguez at vass.es

VASS Consultoría de Sistemas
Tel: +34 931 124 7941
Fax: +34 933 184 291
Avinguda Diagonal 210, 4a planta
08018 Barcelona
 <http://www.vass.es/> www.vass.es

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
<http://vass.es/es/aviso-legal> Aviso Legal. Si no deseara recibir más
comunicaciones por correo electrónico comuníquelo en la siguiente dirección:
<mailto:general at vass.es> general at vass.es  

P Antes de imprimir este mensaje, asegúrese de que es necesario. El medio
ambiente está en nuestra mano.

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://webmail.opencms.org/pipermail/opencms-dev/attachments/20130319/fc851a3a/attachment.htm>