Forum Discussion
Fix the UTC and PST timezone issue
Hi leodec11 , you are calling the function inside your query logic . first we need to isolate where the issue lies whether in you pst/utc function or the main query where you are passing the parameter .
first we need to check if your function is giving the correct result in local machine and server .
I created a sample pbix to check if the timezone converison is givig me the expected result .
I'm based out of CST so the in my macchine system time and CST should match .
in service UTC and System time should match since the service will be defaulted to utc
PQ to get the table :let
// 1. Get Current Times
Source = let
UtcNow = DateTimeZone.UtcNow(),
SystemNow = DateTime.LocalNow(),
// 2. DST Logic
// Get the date from UTC to determine the year
CurrentDate = DateTime.Date(DateTimeZone.RemoveZone(UtcNow)),
CurrentYear = Date.Year(CurrentDate),
// DST Starts: 2nd Sunday in March
StartDST = Date.StartOfWeek(#date(CurrentYear, 3, 14), Day.Sunday),
// DST Ends: 1st Sunday in November
EndDST = Date.StartOfWeek(#date(CurrentYear, 11, 7), Day.Sunday),
IsDST = CurrentDate >= StartDST and CurrentDate < EndDST,
// 3. Set Offsets
CST_Offset = if IsDST then -5 else -6,
PST_Offset = if IsDST then -7 else -8,
// 4. Calculate Zone Times AND REMOVE ZONE info
UTC_Fixed = DateTimeZone.RemoveZone(UtcNow),
CST_Fixed = DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(UtcNow, CST_Offset)),
PST_Fixed = DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(UtcNow, PST_Offset))
in
[
Now_System = SystemNow,
UTC_Time = UTC_Fixed,
CST_Time = CST_Fixed,
PST_Time = PST_Fixed,
Is_DST_Active = IsDST
],// 5. Convert Record to Table
#"Converted to Table" = Record.ToTable(Source),
// 6. Transpose
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
// 7. Promote Headers
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
// 8. Data Types
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
{"Now_System", type datetime},
{"UTC_Time", type datetime},
{"CST_Time", type datetime},
{"PST_Time", type datetime},
{"Is_DST_Active", type logical}
})
in
#"Changed Type"
now you check if this works as expected in service , then use the max(pst) in your main query logic
Thanks
If this response was helpful in any way, I’d gladly accept a kudo.
Please mark it as the correct solution. It helps other community members find their way faster
Super UserHii leodec11
Your logic is mixing DateTimeZone.RemoveZone() with manual hour offsets, which causes the date to shift after publishing. Instead of manually subtracting hours, convert UTC properly using DateTimeZone.SwitchZone() and keep the value timezone-aware until the final step. For example:
Source = DateTimeZone.UtcNow(),
PST = DateTimeZone.SwitchZone(Source, -8),
PST_Date = Date.From(PST)Do not remove the zone before conversion. The issue occurs because the Service evaluates UtcNow() in UTC and your manual offset logic results in the next-day shift.
hello Rohit
Thanks for your reply.
Below is the Power Query M code : - let
Source = DateTimeZone.UtcNow(),
PST = DateTimeZone.SwitchZone(Source, -8),
PSTDate = Date.From(PST),
#"Extracted Date" = Date.From(PSTDate)
in
#"Extracted Date"
then used pstdate as parameter in oracle query let
PSTDateTime = DateTimeZone.SwitchZone(DateTimeZone.UtcNow(), -8),
PSTDate = DateTime.Date(PSTDateTime),
DateText = Date.ToText(PSTDate, "yyyy-MM-dd"),
Source = Oracle.Database(
"Servername",
[
HierarchicalNavigation = true,
Query =
"with cte_kiosk_dep as (#(lf)
select distinct dep.department_id, dep.department_name, dep.specialty, dep.rev_loc_id #(lf)
from hcclsc_lp.PAT_ENC enc #(lf)
join hcclsc.access_wrkf wrkf on wrkf.csn = enc.pat_enc_csn_id #(lf)
join hcclsc.clarity_dep dep on dep.department_id = enc.department_id #(lf)
where enc.contact_date >= trunc(DATE '" & DateText & "', 'year') #(lf)
and wrkf.metric_id = 9001700 #(lf)
), #(lf)
cte_days_of_week as (#(lf)
select which_days_c day_of_week, count(*) day_of_week_cnt #(lf)
from hcclsc.date_dimension cal #(lf)
left join kpbisc_grp_rivrpt.tbl_calendar_ext hol on hol.calendar_date = cal.calendar_dt #(lf)
where cal.which_days_c in (to_char(DATE '" & DateText & "', 'd'), to_char(DATE '" & DateText & "', 'd')+1) #(lf)
and cal.calendar_dt >= DATE '" & DateText & "' - 35 #(lf)
and cal.calendar_dt <= DATE '" & DateText & "' - 2 #(lf)
and (hol.calendar_date is null or hol.calendar_type <> 'Holiday') #(lf)
group by which_days_c #(lf)
), #(lf)
cte_raw_data as (#(lf)
select case #(lf)
when to_char(DATE '" & DateText & "', 'd') = cnt.day_of_week then DATE '" & DateText & "' #(lf)
else DATE '" & DateText & "' + 1 #(lf)
end as ENC_DATE, #(lf)
cnt.day_of_week, #(lf)
extract(hour from cast(enc.appt_time as timestamp)) as APPT_HOUR, #(lf)
loc.loc_name, #(lf)
cnt.day_of_week_cnt, #(lf)
count(enc.pat_enc_csn_id) as CHECKIN_CNT, #(lf)
count(enc.pat_enc_csn_id)/cnt.day_of_week_cnt as AVG_CHECKIN_CNT #(lf)
from hcclsc_lp.PAT_ENC enc #(lf)
join cte_kiosk_dep dep on dep.department_id = enc.department_id #(lf)
join hcclsc.clarity_loc loc on loc.loc_id = dep.rev_loc_id #(lf)
left join hcclsc.clarity_emp emp on emp.user_id = enc.checkin_user_id #(lf)
left join cte_days_of_week cnt on cnt.day_of_week = to_char(enc.contact_date,'d') #(lf)
left join kpbisc_grp_rivrpt.tbl_calendar_ext hol on hol.calendar_date = enc.contact_date #(lf)
where enc.contact_date between DATE '" & DateText & "' - 35 and DATE '" & DateText & "' - 2 #(lf)
and enc.appt_status_c in (2,6) #(lf)
and enc.enc_type_c not in ('121','129','1248','124') #(lf)
and to_char(enc.contact_date,'d') in (to_char(DATE '" & DateText & "', 'd'), to_char(DATE '" & DateText & "', 'd')+1) #(lf)
and (enc.copay_source_c = 3 or enc.copay_due = 0) #(lf)
and upper(dep.specialty) not in ('LAB','RAD') #(lf)
and loc.rpt_grp_seven = '14' #(lf)
and loc.loc_name not in ('PALM U','REST U') #(lf)
and emp.name is not null #(lf)
and (hol.calendar_date is null or hol.calendar_type <> 'Holiday') #(lf)
group by cnt.day_of_week, extract(hour from cast(enc.appt_time as timestamp)), loc.loc_name, cnt.day_of_week_cnt #(lf)
), #(lf)
cte_raw_data_line as (#(lf)
select dt.*, row_number() over (partition by dt.day_of_week, dt.loc_name order by avg_checkin_cnt desc, appt_hour asc) as LINE #(lf)
from cte_raw_data dt #(lf)
) #(lf)
select * from cte_raw_data_line"
]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"ENC_DATE", type date}})
in
#"Changed Type"
I don't know what i am doing wrong, after publishing it is showing 02/12/26 which is UTC current date where according to PST it is stil 02/11/26.
Please help to fix this.
Regards