Adding time to date

[doespirito]

Hello,

I am working in a newspaper,

It is still printed in the night before the date of publication.

see the qv file attached

In the first line, the printing starts on 04/06/2018, at 1h56. and finish on 04/06/2018.

In the second line, he printing starts on 03/02/2018 at 23h46, but finishs on 04/02/2018 at 00h01.

In these two  cases how can i create the two dates (beginTime and EndTime) with the format DD/MM/YYYY H:S.

my goal is to determine the duration of printing : EndTime - BeginTime .

But i cannot determine good dates to do that.

Can somebody help me ?

Thand You Very Much

Arnault

see the qv file attached

[brunobertels · Accepted solution]

Bonjour Arnault

Rajoute ceci a ton script

Load

….

// crée un champ date de début au format Timestamp

timestamp(released_date+BeginTime,'DD/MM/YYYY hh:mm:ss') as BeginDate,

//Crée un champ date de fin au format Timestamp en fonction de EndTime

if(EndTime-BeginTime<0,

timestamp(released_date+1+EndTime,'DD/MM/YYYY hh:mm:ss'),

timestamp(released_date+EndTime,'DD/MM/YYYY hh:mm:ss'))

as EndDATE

….

From …

Tu obtiens ceci :

Avec ton script :

SET ThousandSep=' ';
SET DecimalSep='.';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep='.';
SET MoneyFormat='# ##0.00 €;-# ##0.00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';
SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

LOAD fichier,
     TypeProd,
     jour,
     date,
     DateParution as releasedate,
     JourSem,
     edition,
     nbpages,
     grammage,
     exemplaires,
     Visa,
     HeureDebut as BeginTime,
     HeureFin as EndTime,
     NbCasse,
     Duration,
    
     timestamp(DateParution+HeureDebut,'DD/MM/YYYY hh:mm:ss') as BeginDate,
if(HeureFin-HeureDebut<0,
timestamp(DateParution+1+HeureFin,'DD/MM/YYYY hh:mm:ss'),
timestamp(DateParution+HeureFin,'DD/MM/YYYY hh:mm:ss'))
as EndDATE,

time(
if(HeureFin-HeureDebut<0,
timestamp(DateParution+1+HeureFin,'DD/MM/YYYY hh:mm:ss'),
timestamp(DateParution+HeureFin,'DD/MM/YYYY hh:mm:ss'))
-
timestamp(DateParution+HeureDebut,'DD/MM/YYYY hh:mm:ss'),'hh:mm:ss')as Durée
    
    
     //test
FROM

(txt, utf8, embedded labels, delimiter is ',', msq);

[doespirito]

Merci beaucoup Bruno,

ça marche parfaitement.

Bonne fin de journée,

Arnault