The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi team,
I am working on a project where I need to bring time from different timezones.
In order to do this, i need to use the utc time as my starting point and then do the offsets.
How can i skip the local time it takes below and bring the UTC one?
Please see the code below
{
"$schema": "https://vega.github.io/schema/vega/v5.json",
"description": "A circular clock visualization showing the current time.",
"width": 100,
"height": 100,
"signals": [
{"name": "centerX", "init": "width/2"},
{"name": "centerY", "init": "height/2"},
{"name": "radiusRef", "init": "min(width,height)*0.8"},
{"name": "sizeFactor", "init": "radiusRef/400"},
{"name": "outerRadius", "init": "radiusRef/2"},
{"name": "innerRadius", "init": "radiusRef/2 - (10 * sizeFactor)"},
{
"name": "currentDate",
"init": "now()",
"on": [{"events": {"type": "timer", "throttle": 1000}, "update": "now()"}]
},
{
"name": "currentHour",
"init": "hours(currentDate)+minutes(currentDate)/60",
"on": [
{
"events": {"signal": "currentDate"},
"update": "hours(currentDate)+minutes(currentDate)/60"
}
]
},
{
"name": "currentMinute",
"init": "minutes(currentDate)+seconds(currentDate)/60",
"on": [
{
"events": {"signal": "currentDate"},
"update": "minutes(currentDate)+seconds(currentDate)/60"
}
]
},
{
"name": "currentSecond",
"init": "seconds(currentDate)",
"on": [
{"events": {"signal": "currentDate"}, "update": "seconds(currentDate)"}
]
}
],
"data": [
{
"name": "hours",
"transform": [
{"type": "sequence", "start": 0, "stop": 12, "step": 1, "as": "hour"},
{
"type": "formula",
"expr": "centerX - cos(PI/2 + (datum.hour * PI/6)) * (outerRadius - (outerRadius-innerRadius)/2)",
"as": "x"
},
{
"type": "formula",
"expr": "centerY - sin(PI/2 + (datum.hour * PI/6)) * (outerRadius - (outerRadius-innerRadius)/2)",
"as": "y"
},
{
"type": "formula",
"expr": "centerX - cos(PI/2 + (datum.hour * PI/6)) * (innerRadius - 25 * max(sizeFactor, 0.4))",
"as": "xHour"
},
{
"type": "formula",
"expr": "centerY - sin(PI/2 + (datum.hour * PI/6)) * (innerRadius - 25 * max(sizeFactor, 0.4))",
"as": "yHour"
}
]
},
{
"name": "minutes",
"transform": [
{"type": "sequence", "start": 0, "stop": 60, "step": 1, "as": "minute"},
{
"type": "formula",
"expr": "centerX - cos(PI/2 + (datum.minute * PI/30)) * (outerRadius - (outerRadius-innerRadius)/3)",
"as": "x"
},
{
"type": "formula",
"expr": "centerY - sin(PI/2 + (datum.minute * PI/30)) * (outerRadius - (outerRadius-innerRadius)/2)",
"as": "y"
}
]
}
],
"scales": [
{
"name": "hourScale",
"domain": {"data": "hours", "field": "hour"},
"range": [0, {"signal": "2*PI"}]
},
{
"name": "minutesScale",
"domain": {"data": "minutes", "field": "minute"},
"range": [0, {"signal": "2*PI"}]
}
],
"marks": [
{
"type": "arc",
"encode": {
"enter": {
"x": {"signal": "centerX"},
"y": {"signal": "centerY"},
"startAngle": {"value": 0},
"endAngle": {"signal": "2*PI"},
"outerRadius": {"signal": "outerRadius"},
"fill": {"value": "transparent"},
"stroke": {"value": "black"}
}
}
},
{
"type": "arc",
"encode": {
"enter": {
"x": {"signal": "centerX"},
"y": {"signal": "centerY"},
"startAngle": {"value": 0},
"endAngle": {"signal": "2*PI"},
"outerRadius": {"signal": "innerRadius"},
"fill": {"value": "#FCFCFC"},
"opacity": {"value": 0.6},
"stroke": {"value": "black"}
}
}
},
{
"type": "symbol",
"from": {"data": "minutes"},
"encode": {
"enter": {
"size": {"signal": "pow(2*sizeFactor, 2)"},
"x": {"field": "x"},
"y": {"field": "y"},
"angle": {"signal": "datum.minute*6"},
"shape": {"value": "m 0 -5 v 10"},
"stroke": {"value": "black"}
}
}
},
{
"type": "symbol",
"from": {"data": "hours"},
"encode": {
"enter": {
"size": {"signal": "pow(2*sizeFactor, 2)"},
"angle": {"signal": "datum.hour*30"},
"shape": {"value": "m -5 -5 h 10 l -5 10 l -5 -10 Z"},
"x": {"field": "x"},
"y": {"field": "y"},
"fill": {"value": "black"}
}
}
},
{
"type": "text",
"from": {"data": "hours"},
"encode": {
"enter": {
"x": {"field": "xHour"},
"y": {"field": "yHour"},
"align": {"value": "center"},
"baseline": {"value": "middle"},
"text": {"signal": "datum.hour === 0 ? 12 : datum.hour"},
"fontSize": {"signal": "25*max(sizeFactor, 0.4)"}
}
}
},
{
"type": "symbol",
"encode": {
"enter": {
"size": {"signal": "pow(2*sizeFactor, 2)"},
"shape": {"value": "M 0 0 h-1 l 1 -160 l 1 160 h-1"},
"x": {"signal": "centerX"},
"y": {"signal": "centerY"},
"fill": {"value": "red"},
"stroke": {"value": "red"},
"strokeSize": {"value": 2},
"strokeCap": {"value": "round"}
},
"update": {"angle": {"signal": "currentSecond*6"}}
}
},
{
"type": "symbol",
"encode": {
"enter": {
"size": {"signal": "pow(2*sizeFactor, 2)"},
"shape": {
"value": "M 0 0 h -2 l -5 -30 l 7 -120 l 7 120 l -5 30 h -2 "
},
"x": {"signal": "centerX"},
"y": {"signal": "centerY"},
"fill": {"value": "black"},
"stroke": {"value": "grey"},
"strokeCap": {"value": "round"},
"strokeWidth": {"signal": "2* min(sizeFactor, 1)"}
},
"update": {"angle": {"signal": "currentMinute*6"}}
}
},
{
"type": "symbol",
"encode": {
"enter": {
"size": {"signal": "pow(2*sizeFactor, 2)"},
"shape": {"value": "M 0 0 h-3 l -4 -30 l 7 -80 l 7 80 l -4 30 h -3"},
"x": {"signal": "centerX"},
"y": {"signal": "centerY"},
"fill": {"value": "black"},
"stroke": {"value": "grey"},
"strokeCap": {"value": "round"},
"strokeWidth": {"signal": "2* min(sizeFactor, 1)"},
"zIndex": {"value": 1}
},
"update": {"angle": {"signal": "currentHour*30"}}
}
},
{
"type": "arc",
"encode": {
"enter": {
"x": {"signal": "centerX"},
"y": {"signal": "centerY"},
"startAngle": {"value": 0},
"endAngle": {"signal": "2*PI"},
"outerRadius": {"signal": "6*sizeFactor"},
"fill": {"value": "black"},
"stroke": {"value": "grey"},
"zIndex": {"value": 1}
}
}
},
{
"type": "arc",
"encode": {
"enter": {
"x": {"signal": "centerX"},
"y": {"signal": "centerY"},
"startAngle": {"value": 0},
"endAngle": {"signal": "2*PI"},
"innerRadius": {"signal": "outerRadius"},
"outerRadius": {"signal": "outerRadius + 14 * sizeFactor"},
"fill": {"value": "#333"},
"stroke": {"value": "grey"}
}
}
}
]
}
Hi @CK1992,
For explicit UTC, instead of now() you can use:
utcOffset('hours', now(), 0)
Regards,
Daniel
Proud to be a Super User!
On how to ask a technical question, if you really want an answer (courtesy of SQLBI)
Hi @dm-p ,
Thanks for your reply. Much appreciated.
This function still takes into account the time of the user's laptop. It does not take the UTC time.
In case i have one clock in Paris by using the utcOffset('hours', now(), 0) and the same clock in Singapore, it would show different time.
This makes sense because inside the function we still use the now() function (laptop's clock).
How can i take just the UTC Time?
I am creating a report where the users from different countries want to check the time in different time zones.
Could you please help me with it?
Thanks a lot
Hi @dm-p ,
I hope you are well and had a good weekend.
Sorry for chasing. I was wondering if you had a chance to have a look at the aforementioned issue.
Thanks,
CK
Hi - no I haven't been able to dedicate any more time to this, or any other forum questions recently. Also, I live in NZ, which is currently exactly 12 hours ahead of UTC, which makes debugging a 12-hour clock quite difficult from my side of things 😛
If you've tried working with the existing UTC-based expressions and don't have any way forward, you could try the approach from this thread, replacing their measure with one that uses UTCNOW() as a reference point. This will come from the data model and should hopefully guarantee consistency if you can't rely on the client machine's timestamp. Could that work for you?
Proud to be a Super User!
On how to ask a technical question, if you really want an answer (courtesy of SQLBI)