topic Time conversions in QlikView

Time conversions

Anonymous — Tue, 13 Jul 2010 00:16:03 GMT

I have a datetime field Startime which is in GMT and has to be converted to PST ,

Here is my Load statement , how do i convert to PST in load statement ?

Load

starttime

Date

Week

(starttime) AS [Week #]

WeekStart

(starttime) as Week

Year

(starttime) AS Year

Month

(starttime) AS [Month #]

MonthStart

(starttime) as Month

Day

(starttime) AS [Day]

Weekday

(starttime) AS WeekDay

Date

(monthstart(starttime), 'MMM-YYYY')

MonthYear

Week

(starttime)&'-'&Year(starttime) AS WeekYear

hour

(starttime) as BuildHour

Time conversions

Tue, 13 Jul 2010 02:24:23 GMT

Use this function:

ConvertToLocalTime(starttime, "GMT-08:00")

Time conversions

Anonymous — Tue, 13 Jul 2010 20:39:20 GMT

I am getting error saying coverttolocaltime is not recognizable

Here is my code could any one please let me know what is wrong ...

[ code ]

Load

ConvertToLocalTime

Date

Week

WeekStart

Year

Month

MonthStart

Day

Weekday

Date

(monthstart(ConvertToLocalTime(starttime,"GMT-07:00")), 'MMM-YYYY') (ConvertToLocalTime(starttime,"GMT-07:00")) AS WeekDay (ConvertToLocalTime(starttime,"GMT-07:00")) AS [Day] (ConvertToLocalTime(starttime,"GMT-07:00")) as Month (ConvertToLocalTime(starttime,"GMT-07:00")) AS [Month #] (ConvertToLocalTime(starttime,"GMT-07:00")) AS Year (ConvertToLocalTime(starttime,"GMT-07:00")) as Week ,(ConvertToLocalTime(starttime,"GMT-07:00")) AS [Week #] ,,(starttime,"GMT-07:00") as starttime ,

MonthYear,

Week

hour

BuildSizeMB

,BuildID ,Action,Duration,FileCount,ProductLine,Duration_Min ,UserName, JobName ;

SQLsele

Date,BuildID,

((bytecount)/1024/1024)

as 'BuildSizeMB',(DURATION/60)AS Duration_Min,

Action,Duration,FileCount,ProductLine,SubmittedBy

as 'UserName',JobName FROM

ASWTRANS.DBO.CRMBUILDMETRICS [ /code ]

ct starttime,CONVERT(VARCHAR(10), ConvertToLocalTime(starttime,"GMT-07:00"), 101) AS (ConvertToLocalTime(starttime,"GMT-07:00")) as BuildHour (ConvertToLocalTime(starttime,"GMT-07:00"))&'-'&Year(ConvertToLocalTime(starttime,"GMT-07:00")) AS WeekYear

Time conversions

rwunderlich — Tue, 13 Jul 2010 21:41:52 GMT

It's very difficult to read your script as posted. Can you repost as plain text?

1. In Settings -> User Preferences -> Editor tab, uncheck "Copy as RTF".
2. Post without the [ code ] tags.

-Rob

Time conversions

Anonymous — Tue, 13 Jul 2010 21:49:47 GMT

Where do i find Setting?

Time conversions

johnw — Wed, 14 Jul 2010 00:17:44 GMT

At the top of the QlikView screen. It's one of the menu items.

Time conversions

Anonymous — Wed, 14 Jul 2010 00:30:14 GMT

Here is my script,I want to convert starttime to PDT which is in GMT

Load
starttime,
Date,
Week(starttime) AS [Week #],
WeekStart(starttime) as Week,
Year(starttime) AS Year,
Month(starttime) AS [Month #],
MonthStart(starttime) as Month,
Day(starttime) AS [Day],
Weekday(starttime) AS WeekDay,
Date(monthstart(starttime), 'MMM-YYYY') AS
MonthYear,
Week(starttime)&'-'&Year(starttime) AS WeekYear,
hour(starttime) as BuildHour,
BuildSizeMB,BuildID,
Action,Duration,FileCount,ProductLine,Duration_Min,
UserName,JobName
;

SQL select starttime,CONVERT(VARCHAR(10), starttime, 101) AS Date,BuildID,
((bytecount)/1024/1024) as 'BuildSizeMB',(DURATION/60)AS Duration_Min,
Action,Duration,FileCount,ProductLine,SubmittedBy as 'UserName',JobName
FROM ASWTRANS.DBO.CRMBUILDMETRICS

Time conversions

johnw — Wed, 14 Jul 2010 00:52:54 GMT

I think you just need "GMT-08:00" in single instead of double quotes, though since you aren't showing what you got the error on, I could easily be wrong. For that matter, it looks like you can very explicitly refer to pacific time. Perhaps like this:

LOAD
Week(starttime) AS [Week #],
WeekStart(starttime) as Week,
Year(starttime) AS Year,
Month(starttime) AS [Month #],
MonthStart(starttime) as Month,
Day(starttime) AS [Day],
Weekday(starttime) AS WeekDay,
Date(monthstart(starttime), 'MMM-YYYY') AS MonthYear,
Week(starttime)&'-'&Year(starttime) AS WeekYear,
hour(starttime) as BuildHour,
;
Load
converttolocaltime(starttime,'Pacific Time (US & Canada)') as starttime
Date,
BuildSizeMB,BuildID,
Action,Duration,FileCount,ProductLine,Duration_Min,
UserName,JobName
;

Time conversions

Anonymous — Wed, 14 Jul 2010 01:17:06 GMT

So , does 'Pacific Time (US & Canada)' consider day light savings too?

You are right , single quotes work...

converttolocaltime(starttime,'GMT-07:00') works without any errors, i have given 7 as this is day light saving period....

And also , do i have to change this wherever i had startime ? like for Month , Year,day convertions and also select statement ? Or qlikview automatically take the conversion if given in one load statement ?

Time conversions

johnw — Wed, 14 Jul 2010 01:25:00 GMT

According to the help text, "The resulting time is adjusted for daylight savings time, unless the third parameter is set to 1 or true()."

The way I rearranged your load, you don't have to do it anywhere else but that one spot. As I set it up, that happens first, THEN it takes a second pass, using your adjusted starttime as input for all the other functions. The approach is called a preceeding load. It has this basic structure:

LOAD *
,these fields are loaded last, and can use any fields from the below two loads
;
LOAD *
,these fields are loaded second, and can use any fields from the below load
;
LOAD
these fields are loaded first
FROM my data source
;

Time conversions

Anonymous — Wed, 14 Jul 2010 02:05:56 GMT

One note: conversion to Date must be done after convertion from GMT to local time.

Time conversions

Anonymous — Wed, 14 Jul 2010 02:25:57 GMT

This is my current script

LOAD

Week(starttime) AS [Week #],
WeekStart(starttime) as Week,
Year(starttime) AS Year,
Month(starttime) AS [Month #],
MonthStart(starttime) as Month,
Day(starttime) AS [Day],
Weekday(starttime) AS WeekDay,
Date(monthstart(starttime), 'MMM-YYYY') AS MonthYear,
Week(starttime)&'-'&Year(starttime) AS WeekYear,
hour(starttime) as BuildHour,
BuildSizeMB,BuildID,
Action,Duration,FileCount,ProductLine,Duration_Min,
UserName,JobName,Date

;

Load
converttolocaltime(starttime,'Pacific Time (US & Canada)') as starttime,
BuildSizeMB,BuildID,
Action,Duration,FileCount,ProductLine,Duration_Min,
UserName,JobName,Date

;

SQL select starttime,CONVERT(VARCHAR(10),starttime,101) AS Date,BuildID,
((bytecount)/1024/1024) as BuildSizeMB,(DURATION/60)AS Duration_Min,
Action,Duration,FileCount,ProductLine,SubmittedBy as 'UserName',JobName
FROM ASWTRANS.DBO.CRMBUILDMETRICS

Do you think it should be something like below ? , But i get an syntax error saying converttolocaltime is not identfied, One basic question , how do the select statement work here , i got an idea of load statements loading , when does a select statement come into consideration ?before or after load statement?

LOAD

;

Load
converttolocaltime(starttime,'Pacific Time (US & Canada)') as starttime,
BuildSizeMB,BuildID,
Action,Duration,FileCount,ProductLine,Duration_Min,
UserName,JobName,Date

;

SQL select starttime,CONVERT(VARCHAR(10),converttolocaltime(starttime,'Pacific Time (US & Canada)') ,101) AS Date,BuildID,
((bytecount)/1024/1024) as BuildSizeMB,(DURATION/60)AS Duration_Min,
Action,Duration,FileCount,ProductLine,SubmittedBy as 'UserName',JobName
FROM ASWTRANS.DBO.CRMBUILDMETRICS

Time conversions

rwunderlich — Wed, 14 Jul 2010 05:17:31 GMT


RAMYA_BEGINNER wrote:But i get an syntax error saying converttolocaltime is not identfied, One basic question , how do the select statement work here , i got an idea of load statements loading , when does a select statement come into consideration ?before or after load statement?

ConvertToLocalTime is a QV function. It must be used in the LOAD statement, not in the SQL Select. Only valid SQL may be used in the SQL Select.

The SQL Select is executed first. The data returned by the SQL Select is "passed up" to the LOAD statement.

-Rob

Time conversions

Anonymous — Wed, 14 Jul 2010 17:47:48 GMT

I changed my Script to below , but i am getting all duplicate values for date, to avoid this i used convert function in select statement previously , as converttolocaltime cannot be used in sql statement and convert(which converts datetime value to date in 101 form) cannot be used in load statement i cannot understand how to go with this...

By the way how do i attach a qlikview file to my post , i tried using insert media , did not work ...

Please Help..

LOAD

Week(starttime) AS [Week #],
WeekStart(starttime) as Week,
Year(starttime) AS Year,
Month(starttime) AS [Month #],
MonthStart(starttime) as Month,
Day(starttime) AS [Day],
Weekday(starttime) AS WeekDay,
Date(starttime) as Date,
Date(monthstart(starttime), 'MMM-YYYY') AS MonthYear,
Week(starttime)&'-'&Year(starttime) AS WeekYear,
hour(starttime) as BuildHour,
BuildSizeMB,BuildID,
Action,Duration,FileCount,ProductLine,Duration_Min,
UserName,JobName

;

Load
converttolocaltime(starttime,'Pacific Time (US & Canada)') as starttime,
BuildSizeMB,BuildID,
Action,Duration,FileCount,ProductLine,Duration_Min,
UserName,JobName

;

SQL select starttime,BuildID,
((bytecount)/1024/1024) as BuildSizeMB,(DURATION/60)AS Duration_Min,
Action,Duration,FileCount,ProductLine,SubmittedBy as 'UserName',JobName
FROM ASWTRANS.DBO.CRMBUILDMETRICS

Time conversions

boorgura — Wed, 14 Jul 2010 18:31:47 GMT

Hey Ramya,

you can use the floor function here.

Lets say for a give day, you have multiple timestamps.

then,

date(floor(timestamp#('<time_stamp>', 'FORMAT')))

or try date(floor(date#('<time_stamp>', 'FORMAT')))

here FORMAT is the format in which we get the data.

Let me know if it works.

Time conversions

Anonymous — Wed, 14 Jul 2010 19:07:54 GMT

Both of them did not work 😞

Here is my code, i dont get any error with below, even i dont get any data in the field Data

LOAD

Week(starttime) AS [Week #],
WeekStart(starttime) as Week,
Year(starttime) AS Year,
Month(starttime) AS [Month #],
MonthStart(starttime) as Month,
Day(starttime) AS [Day],
Weekday(starttime) AS WeekDay,
//Date(starttime) as Date,
Date(monthstart(starttime), 'MMM-YYYY') AS MonthYear,
Week(starttime)&'-'&Year(starttime) AS WeekYear,
hour(starttime) as BuildHour,
BuildSizeMB,BuildID,
Action,Duration,FileCount,ProductLine,Duration_Min,
UserName,JobName,
//date(floor(starttime('starttime', 'MM/DD/YYYY'))) as Date,
date(floor(date#('starttime', 'MM/DD/YYYY')))as Date

;

Load
converttolocaltime(starttime,'Pacific Time (US & Canada)') as starttime,
BuildSizeMB,BuildID,
Action,Duration,FileCount,ProductLine,Duration_Min,
UserName,JobName

;

Time conversions

Anonymous — Wed, 14 Jul 2010 20:06:42 GMT

All looks correct excpet the "Date" field. It should be simply:
date(floor(starttime)) as Date
or, if you want to specify format explicitly:
date(floor(starttime), 'DD/MM/YY') as Date

Time conversions

Anonymous — Wed, 14 Jul 2010 23:18:03 GMT

This works with the filter , but in current selection box, i see the time 12:00am , how do i avoid that...

Time conversions

Anonymous — Wed, 14 Jul 2010 23:43:09 GMT

Go to Document Properties, tab "Number", and make sure that field Date is Date, not Timestamp nor Time.
You can also do this in list box properties, but it will effect only this list box. It's better on document level.

Time conversions

Anonymous — Thu, 15 Jul 2010 00:12:46 GMT

Got it, Thank you ...