Forum Discussion
Get Time from DateTime field
Hi @Thankyouverymuc ,
Here a solution for a calculated column as described in this blog post as well:
https://www.tackytech.blog/how-to-crack-the-mystery-of-the-mighty-dax/#22_How_to_retrieve_the_time_from_a_datetime_columnWe start by duplicating the datetime column:
--> Right click on a column in your table and select new column:
--> duplicate the column by referencing the datetime column:
NewColumn = TableDateTime[DateTime]Then, just change the format to Time (just like @Syk suggested in Power Query):
The result:
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/Sorry to jump in on this forum but noticed there wasn't a DAX solution on here. If you're looking for a DAX solution, you can extract the Time from a DateTime column by doing the following:
1. Click on new Calculated Column2. Time = FORMAT ( 'TableName'[DateTimeColumnName] , "hh:mm:ss" )
3. Convert the new Time column from Text to Time.
Hope this helps.
Theo
Sorry to jump in on this forum but noticed there wasn't a DAX solution on here. If you're looking for a DAX solution, you can extract the Time from a DateTime column by doing the following:
1. Click on new Calculated Column
2. Time = FORMAT ( 'TableName'[DateTimeColumnName] , "hh:mm:ss" )
3. Convert the new Time column from Text to Time.
Hope this helps.
Theo
How would I control what timezeone is used when the datetime is formatted? i.e. the datetime is stored in UTC but I want to format it into a fixed timezone (for example US/Eastern).
Hi arothberg, you need to know the difference in hours between UTC and your target timezone. Once you know that, then all you need to do is create a calculated column:
AdjustedTime = FORMAT ( 'Table'[TimeColumn] - TIME ( 0 , 0 , 0 ) , "hh:mm:ss" )The ( 0 , 0 , 0 ) represents Hours , Minutes , Seconds. So if you want to take 5 hours off your TimeColumn, then just write ( 5 , 0 , 0 ) in the above formula. Make sure to adjust the - / + according to the timezone you're after.Hope this helps.TheoHow would I handle a timezone which has a variable offset to UTC (aka day lights savings)? Is there no function that can be used for a calculated column that hanldes proper timezone conversions?
I recently went down a rabbit hole trying to find a way to have Power BI express a time in a different time zone, and I wasn't able to find anything. I resort to fixing these things in SQL Server (e.g. [myUTCTiemStamp] At Time Zone 'UTC' At Time Zone 'Central Standard Time').
If you always want it converted to AZ time (No daylight savings time), I think you could do
TimeOfDateTime =
TIME( HOUR(qryAdmissions[DischargeDateTime] - TIME(7,0,0)), MINUTE(qryAdmissions[DischargeDateTime]), 0)If you are willing to store hour offsets, you can do that instead of the 7 that was hardcoded above. But - daylight savings time becomes an annoying barrier.
** would love to hear if anyone has a better solution!
(note that this link has some interesting ideas for Powerquery side. Still a giant hassle if you live somewhere with daylight savings time issues. https://radacad.com/solving-dax-time-zone-issue-in-power-bi)
Use this measure to convert UTC to PST or anything.. this is dynamic, based on time , date also will change.. Let utc 12 AM, automatically it will give 8 or 9PM before day .
Current EST_Today =VAR UTC_DateTime = now() // Variable to hold the original UTC datetime value from your table.VAR Year = YEAR(UTC_DateTime) // Extracts the year from the UTC datetime to calculate DST boundaries for that specific year.VAR DST_Start = DATE(Year, 3, 14 - WEEKDAY(DATE(Year, 3, 8), 2)) // Calculates the start of DST (Second Sunday in March).VAR DST_End = DATE(Year, 11, 7 - WEEKDAY(DATE(Year, 11, 1), 2)) // Calculates the end of DST (First Sunday in November).VAR IsDST = IF(UTC_DateTime >= DST_Start && UTC_DateTime < DST_End, -4, -5) // Determines if the datetime falls in DST period; adjusts offset to -4 for DST (EDT), otherwise -5 for standard time (EST).VAR LocalDateTime = UTC_DateTime +IsDST/24RETURN FORMAT(LocalDateTime, "M/DD/YYYY HH:MM AM/PM")
Only thing consistent about Power BI is its inconsistency over the years. Tried the same 3 steps in DAX and am getting 12AM across all records lol.
