select TO_TIMESTAMP(u.creationdate / 1000) from jiveuser u ;
Thank you Sanjay.
When I ruun both your exact query
and one of mine where I refer to creationdate in another table with it,
I receive following error:
Msg 195, Level 15, State 10
'TO_TIMESTAMP' is not a recognized built-in function name.
This is what I'm using with SQL Server 2012:
Microsoft SQL Server Management Studio 11.0.2100.60
Microsoft Analysis Services Client Tools 11.0.2100.60
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.7601.17514
Microsoft .NET Framework 4.0.30319.1008
Operating System 6.1.7601
Any quick thoughts?
The epoch stamps in the tables use ms precision, but MSSQL doesn't have a to_timestamp function, so:
dateadd(s,modificationDate/1000,'1970-01-01') as datetimesec
will work. Hope that helps!
Hi Ed Snajder
What is the suggested method for PostgresSql.
For example, to retrieve creationdate or modificationdate, we normally use TIMESTAMP 'epoch' + creationdate * interval '1 millisecond' as creationdate.
Is that correct approach? How do we consider timezone as well as here?
Usually, if the ms is not critical for my analysis, I will use to_timestamp(creationdate/1000). This will include the TZ. For exmaple, if I take a sample of dates using your method (which is a great method when you do want to consider ms), the result set is:
# SELECT TIMESTAMP 'epoch' + creationdate * interval '1 millisecond' as result FROM jivedocument LIMIT 3;
With to_timestamp the local time and offset are included:
# select to_timestamp(creationdate/1000) FROM jivedocument limit 3;
Great, thank you for your help. Much appreciated.