cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
afernando
Kudo Kingpin
Kudo Kingpin

Save local time

Hello,

 

I'm trying to save local time to a datatimeoffset column in a SQL server. But powerapps converts local time to +00:00 time zone before saving.

 

Does anyone know how to overcome this issue? 

 

I'm planning to generate a text string in the format of local time eg: "2017-04-06 18:30:00.0000000 +11:00" and write it to the database. But I need to retrieve the local timezone for that. 

 

Any idea how can I achieve this? 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Meneghino
Community Champion
Community Champion

Hi @afernando

 

Does this work?

 

Round(24*(Now()-DateTimeValue(Left(Text(Now(),DateTimeFormat.UTC), 16))), 0)

View solution in original post

6 REPLIES 6
Meneghino
Community Champion
Community Champion

Hi @afernando

 

Does this work?

 

Round(24*(Now()-DateTimeValue(Left(Text(Now(),DateTimeFormat.UTC), 16))), 0)

@Meneghino

works like a charm. Legend!

Hi,

 

While I was able to generate a string that looks like the UTC local time, I'm not having any luck writing the string to the SQL database.

My string looks like this;

 

2017-04-07 13:50:00.0000000 +10:00

 

I can paste this value to the field when connected to the database from MS Acess but PowerApps won't write the value to the field. 

 

Any suggestions?

 

Thanks in advance.

Meneghino
Community Champion
Community Champion

Hi @afernando

With the current state of PowerApps I would suggest to saving UTC times in a datetimeoffset type of column.

I would not recommend saving local time.

Alternatively, you could save to an integer column yyyymmddhhmmss.  This will allow you to filter and sort properly in PowerApps (currently not possible with datetime columns, I have other posts on this)

Then, when the PowerApps team sort out the problems with datetime columns we can finally migrate back to proper datetime columns.

@Meneghino,

 

Thanks for your comment. The problem with saving UTC time is that PowerBI won't let me convert them to local time when connected via DirectQuery. 

The main purpose of my app is to record our production and show the output on a live dashboard. Since DirectQuery doesn't allow converting data types, saving time an integer also not a solution. 

 

I guess I'll have to look somewhere else. 

 

Thanks again.

Anton

Meneghino
Community Champion
Community Champion

Understood.

I would create a calculated column in your data source that turns UTC to local time.  Then you can direct query that column instead without any loss of performance.  You can even index the calculated column.

This way you can keep the best practice of saving UTC.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (1,667)