topic Re: Need help with time stamp conversion in QlikView

Need help with time stamp conversion

Fri, 19 Jun 2015 10:40:38 GMT

I am fetching data from db and storing the data in qvd . in this i am getting values of a date time field as number such as

1426807749 but when i am trying to convert this into time stamp .i am not getting the desired result when i am trying as below

MakeDate(Year(start_time),Month(start_time),Day(start_time)) as Outage_StartTime,

timestamp(start_time) as start_time,

Please suggest how can we convert this into timestamp .

Re: Need help with time stamp conversion

sunny_talwar — Fri, 19 Jun 2015 10:44:49 GMT

what date 1426807749 this correspond to?

Re: Need help with time stamp conversion

Fri, 19 Jun 2015 10:48:00 GMT

Your excel file data doesn't come from dates.

Re: Need help with time stamp conversion

robert_mika — Fri, 19 Jun 2015 10:52:16 GMT

What's the database are using?

Re: Need help with time stamp conversion

Anonymous — Fri, 19 Jun 2015 10:52:55 GMT

See if this helps:

Re: Date ?

Re: Need help with time stamp conversion

Fri, 19 Jun 2015 10:55:02 GMT

Yes thats a date time field

its value should be 2015-03-19 17:29:09 . but when i am getting it in qvd its getting changed in number

Re: Need help with time stamp conversion

MarcoWedel — Fri, 19 Jun 2015 10:58:13 GMT

Hi,

try with

Timestamp(25569 + EpochTimeField / 86400)

so in your case 1426807749 would result in a timestamp 03/19/2015 23:29:09

Re: UNIX DATE to Qlikview DATE

hope this helps

regards

Marco

Re: Need help with time stamp conversion

Fri, 19 Jun 2015 11:25:38 GMT

Yes you are right Marco date is fine now but i can see the value as 03/19/2015 11:29:09 PM

the date is fine but when i see in time for same field in the earlier data which i was getting it 5:29:09 PM .. is there anything which is missing here

Re: Need help with time stamp conversion

MarcoWedel — Fri, 19 Jun 2015 15:41:04 GMT

maybe localization:

=ConvertToLocalTime(Timestamp(25569 + 1426807749 / 86400),'GMT-06:00')

results in 03/19/2015 5:29:09 PM

Does this match your time zone?

regards

Marco

Re: Need help with time stamp conversion

Fri, 19 Jun 2015 18:08:37 GMT

Thanks .. some of the date value is null which is getting converted 01/01/1970 12:00:00 AM but i want to convert that value to now() i.e. current time

Re: Need help with time stamp conversion

sunny_talwar — Fri, 19 Jun 2015 20:02:17 GMT

Try this:

If(Len(Trim(start_time)) > 0, ConvertToLocalTime(Timestamp(25569 + start_time/86400),'GMT-06:00'), Now())

Re: Need help with time stamp conversion

MarcoWedel — Fri, 19 Jun 2015 20:02:25 GMT

=ConvertToLocalTime(Alt(yourtimestampvalue,Now()),'GMT-06:00')

Re: Need help with time stamp conversion

MarcoWedel — Fri, 19 Jun 2015 21:10:41 GMT

yourtimestampvalue in above expression has to be 25569+yourtimestampvalue/86400 instead.

So the whole expression should read in your case:

=ConvertToLocalTime(Alt(25569+start_time/86400,Now()),'GMT-06:00')

one example:

hope this helps

regards

Marco