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

[Isaac R. Higgins]

try Mysql SUBSTRING
 
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substr

mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'

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

Or, since it's a number, you could divide by 1000 and round down to remove the last 3 digits 

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

-----Original Message-----
From: opencms-dev-bounces at opencms.org on behalf of Àlex Rodríguez Bacardit
Sent: Tue 3/19/2013 10:04 AM
To: opencms-dev at opencms.org
Subject: [opencms-dev] [MySQL] exporting publishing dates in custom format
 
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 --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 4502 bytes
Desc: not available
URL: <https://webmail.opencms.org/pipermail/opencms-dev/attachments/20130319/8a6e2ddb/attachment.bin>