topic Re: Timestamp, Datestamp, Time, Date etc etc etc in QlikView

Timestamp, Datestamp, Time, Date etc etc etc

dmac1971 — Thu, 19 Nov 2015 15:27:17 GMT

Need a definitive guide on extracting date and time from a timestamp, having diffs with it!

Data comes in from a csv file, in the file the data is displayed as "01/07/2015 06:53:00", once imported into QV without any changes the format changes to "01-07-2015 06:53:00". So it looks like QV has recognised the data as a date etc.

To extract a workable date I'm using :

Date(Timestamp#([Start Date and Time],'DD-MM-YYYY')) As StartDate,

And this seems to work ok as I can then generate my calendar etc. How do I extract the time, and just the time?

Re: Timestamp, Datestamp, Time, Date etc etc etc

sunny_talwar — Thu, 19 Nov 2015 15:32:12 GMT

Try this:

Date

Date(Floor(Timestamp#([Start Date and Time],'DD-MM-YYYY hh:mm:ss'))) As StartDate,

Time

Time(Frac(Timestamp#([Start Date and Time],'DD-MM-YYYY hh:mm:ss'))) As StartTime,

Re: Timestamp, Datestamp, Time, Date etc etc etc

dmac1971 — Thu, 19 Nov 2015 15:36:30 GMT

Gives me nothing, thanks though.

Re: Timestamp, Datestamp, Time, Date etc etc etc

sunny_talwar — Thu, 19 Nov 2015 15:38:23 GMT

Try this:

Date

Date(Floor(Timestamp#([Start Date and Time],'DD/MM/YYYY hh:mm:ss'))) As StartDate,

Time

Time(Frac(Timestamp#([Start Date and Time],'DD/MM/YYYY hh:mm:ss'))) As StartTime,

Date

Date(Floor([Start Date and Time])) As StartDate,

Time

Time(Frac([Start Date and Time])) As StartTime,

Re: Timestamp, Datestamp, Time, Date etc etc etc

settu_periasamy — Thu, 19 Nov 2015 15:40:06 GMT

Check your Data...

"01/07/2015 06:53:00" (did you copy this or just manual type)

- 2 space between Date and Time (if it is single space, Sunny's suggestion will work)

=Timestamp(Timestamp#('01/07/2015 06:53:00','DD/MM/YYYY hh:mm:ss'),'hh:mm:ss')

Re: Timestamp, Datestamp, Time, Date etc etc etc

Colin-Albert — Thu, 19 Nov 2015 15:41:36 GMT

These blogs detail how to process Date & Time data correctly

https://community.qlik.com/blogs/qlikviewdesignblog/2012/06/07/get-the-dates-right

https://community.qlik.com/docs/DOC-3102

Re: Timestamp, Datestamp, Time, Date etc etc etc

dmac1971 — Thu, 19 Nov 2015 16:05:23 GMT

Thanks for replies all but nothing is working. Settu well spotted there are indeed two spaces between the date element and the time element, this is in the raw data.

I suppose I could use text commands such as Left and Mid to extract something useable but I'd rather crack this so I can refer to the methodology in future.

Will read up a bit more to see if I can get a solution.

Re: Timestamp, Datestamp, Time, Date etc etc etc

dmac1971 — Thu, 19 Nov 2015 16:29:14 GMT

Attachment should show time formats. Still struggling, there are indeed two spaces between date and time in the timestamp.

Re: Timestamp, Datestamp, Time, Date etc etc etc

sunny_talwar — Thu, 19 Nov 2015 16:30:19 GMT

Try it with two spaces where we specify format:

Date

Date(Floor(Timestamp#([Start Date and Time],'DD/MM/YYYY hh:mm:ss'))) As StartDate,

Time

Time(Frac(Timestamp#([Start Date and Time],'DD/MM/YYYY hh:mm:ss'))) As StartTime,

Re: Timestamp, Datestamp, Time, Date etc etc etc

Peter_Cammaert — Thu, 19 Nov 2015 16:40:58 GMT

This works on your csv file:

LOAD [Start Date and Time],

Time(Timestamp#([Start Date and Time],'DD/MM/YYYY hh:mm')) AS [Start Time],

Date(Timestamp#([Start Date and Time],'DD/MM/YYYY hh:mm')) AS [Start Date],

[End Date and Time],

Time(Timestamp#([End Date and Time],'DD/MM/YYYY hh:mm')) AS [End Time],

Date(Timestamp#([End Date and Time],'DD/MM/YYYY hh:mm')) AS [End Date]

FROM [callLog_18-11-2015_18-11-2015.csv](txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Produces this in a Table Box:

Best,

Peter

Re: Timestamp, Datestamp, Time, Date etc etc etc

settu_periasamy — Thu, 19 Nov 2015 16:43:08 GMT

Try this with 2 space between yyyy and hh

=timestamp(timestamp#(field,'DD/MM/YYYY hh:mm:ss'),'hh:mm:ss')