- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
13 digit time Stamp to Date and Time
How do I convert a 13 digit timeStamp to Date (DD/MM/YYYY) and Time (HH:MM:SS).
For example: 1427345947170.
How do I convert this to Date and time format.
Thanks.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Worked around it with the help of your replies and I got the answer.
Thank you guys.
Timestamp(25569 + Round(Parse_Timestamp/1000) / 86400) as Timestamp,
Date((25569 + Round(Parse_Timestamp/1000) / 86400), 'DD/MM/YYYY') as Date,
Time((25569 + Round(Parse_Timestamp/1000) / 86400), 'hh:mm:ss TT') as Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What does the value 1427345947170 represent? Is it number of seconds, minutes, hours or something else? What date and time does 0 represent?
It will be possible to help you define a conversion when you have answered the questions above.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
date(date('1/1/1970')+floor(A/1000/3600/24),'MM/DD/YYYY') as Date
time(time('00:00:00')+frac(A/1000/3600/24),'hh:mm:ss.fff') as Time
Hope this helps.
PFA
Regards,
Andrei
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Looks like Unix epoch date timestamp, have a look at this thread.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi try this,
time(Timestamp(1427345947170)) as time,
date(Timestamp(1427345947170)) as date,
cheers,
Ganesh
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Try
Timestamp((Date/86400)+25569)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Worked around it with the help of your replies and I got the answer.
Thank you guys.
Timestamp(25569 + Round(Parse_Timestamp/1000) / 86400) as Timestamp,
Date((25569 + Round(Parse_Timestamp/1000) / 86400), 'DD/MM/YYYY') as Date,
Time((25569 + Round(Parse_Timestamp/1000) / 86400), 'hh:mm:ss TT') as Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This function is very helpful to anyone who has the date in number format like this:
1563123600000.
I still dont know what is the format of this, but the function his helpful.