Get each date info from TimeStamp

[#1]

Hello,

I have a column called "Fecha" in TimeStamp as the img below:

I want to extract each date info from this ( Hour, Year, Month, Week, Day, Weekday) to make different filters from this. I used the following code:

Hour(Fecha) as Hora,

Year(Fecha) as Año.

month(Fecha) as Mes,

week(Fecha) as Semana,

day(Fecha) as Dia,

WeekDay(Fecha) as Dia_Semana,

But i got two problems. The first one, is that the Year is showing another info:

And when i try to get the Hour from the Timestamps, qlikview doesnt respond and i have to closed it.

Anyone know another form to get the Year and Hour info from this? I searched for differents solutions but anyone helped.

Thank you!

[sunny_talwar]

Images aren't showing up... can you repost

[Gysbert_Wassenaar]

That looks like a unix timestamp. Try =Date(MakeDate(1970, 1, 1) + (Fecha / 86400))

[MarcoWedel]

Hi,

guessing from your sample values, your Fecha timestamps might have millisecond resolution.

So one way of converting them into proper QV timestamps might be:

tabFecha:
LOAD *,
    Hour(Fecha)    as Hora,
    Year(Fecha)    as Año,
    Month(Fecha)  as Mes,
    Week(Fecha)    as Semana,
    Day(Fecha)    as Dia,
    WeekDay(Fecha) as Dia_Semana;
LOAD Fecha as FechaNum,
    Timestamp(25569+Fecha/864E5) as Fecha
INLINE [
    Fecha
    1,49847E+12
    1,49848E+12
    1,49849E+12
    1,49850E+12
    1,49851E+12
    1,49852E+12
    1,49853E+12
    1,49854E+12
    1,49855E+12
    1,49856E+12
    1,49857E+12
] (delimiter is spaces);

hope this helps

regards

Marco

[#5]

Thank you so much! But now i have the problem that i cant match tabFecha table with the tables with all the data to filter it.

I tried match the number of each date but doesnt match anyway:

1 = Date field in datatable

2=  Date field in tabFecha

If i try to convert it to number doesn match because each number show me it as:

My original Data table have this kind of data:

Im looking for another way to do it but i cant find something yet.

Thank you!

[MarcoWedel]

Are you matching tables using the Fecha field?

You might have some small differences in timestamp values you want to match due to rounding issues.

See also:

Correct Time Arithmetic | Qlikview Cookbook  by Rob Wunderlich

Rounding Errors

convert seconds to hh:mm:ss in script

Reference DateTime for calculating occupancy

hope this helps

regards

Marco