Have you tried a function like:
In Tableau's conversion of an epoch integer to a date time, Tableau treats a value of 1 as 24 hours. If in your epoch integer 24 hours = 86400, you will need to divide your integer first.
Which epoch are you using? The Unix epoch is typically 1/1/1970, while the Microsoft COM epoch is 12/30/1899. You need to know your epoch, and then it's a simple calculation such as:
Good to know. In doing the above I also noticed that the dates that were returned were 70 years off. Which indicated that the epoch on the source system (unix) was probably 1970 while Tableau is using an epoch start year of 1900. So I had to add 70 years of seconds back in.
Ah. Robert, I like your formula better. No hardcoded magic numbers for people to reverse engineer.
I'm trying to do something similar. I have two columns in my data defining the date: year & month. When I bring this into Tableau, they appear as number fields so I can't use them as a timescale. In Excel there is the "DATE(year, month, day)" function where you can concatenate individual dateparts, is there a similar function I can use in Tableau? I don't think I can implement the above solution because months and years aren't always of equal lengths. Any ideas?
Tableau has a similar DATE() function as well. Here's a formula, assuming Year is a 2 or 4 digit year and month is 1-12:
DATE(STR([Year])+ "-" + STR([Month]) + "-1")
Thanks, Jonathan! Missed that somehow, works perfectly!
Thank you Joe, your comment helped me solve a problem I was facing in Tableau. I had a measure that shows total time in seconds. I needed to take the average time in seconds and then convert this to show hh:mm:ss. The example Tableau gives for datetime within the calc tool is very confusing and not very helpful - it says the datetime calc requires quotations within the parenthesis. Thanks for showing me I don't need parenthesis in there and also explaining that diving 1 unit by 86400 will give me the 1 second I need in the datetime calc.
Jonathan Drummey I tried using the solution you gave Ben as I have the same situation. But when I tried it, I get an Oracle error saying "ORA-01861: literal does not match format string". Am I doing something wrong?
I'm not familiar with Oracle these days, so I can't give you an answer. However, Tableau has advanced quite a bit since then, check out the DATEPARSE() function. (Answering from my phone so I don't have a link handy.)
Thanks, Jonathan! I'll check that out.
DATE(STR([Year])+ "-" + STR([Month]) + "-1") Works like a charm! Thanks Jonathan!
1 of 1 people found this helpful
How to convert "20160203121254" into DD/MM/YYYY HH:MM:SS format, as it is in reverse order.
Try creating a Calculated Field:
Then Define a format for it under Default Properties in the format you mentioned.