How to Convert UTC Timestamp to Local Time

CopyStorm always stores timestamp value using UTC — exactly like Salesforce. This article demonstrates how to convert a UTC datetime column to a timestamp in your local timezone.

MySQL

select 
	AA.systemmodstamp
    , CONVERT_TZ( AA.systemmodstamp, '+00:00', '-06:00')
from Account AA;

Oracle

SELECT
    AA.systemmodstamp
    , (FROM_TZ(AA.systemmodstamp,'+00:00') AT TIME ZONE 'US/Eastern') localtime

FROM Account AA

PostgreSQL

select 
	AA.systemmodstamp
	, AA.systemmodstamp at time zone 'utc' at time zone 'pst' as localtime
from Account AA;

SQL/Server

SELECT AA.systemmodstamp, CONVERT(datetime, 
               SWITCHOFFSET(CONVERT(datetimeoffset, 
                                    AA.systemmodstamp), 
                            DATENAME(TzOffset, SYSDATETIMEOFFSET()))) 
       AS ColumnInLocalTime
FROM Account AA