How do I convert a timestamp to another timezone with SQL?

Have you ever had that request?

I would like to have the distribution of clicks per hour for the last week in local time

It’s a no brainer if your database server is in the same time zone as your business audience  – then you just need to aggreate the clicks’ timestamps to an hour.

Not sure how to do this?

 Check out my article on extracting part of a timestamp

If, however, the database server is located in a timezone different from the one where your audience is, then you’ll have to do some extra work on your query

Remember how to add time intervals to a timestamp?

This is another case when those functions come in handy

So, assuming your servers are located in New York and business audience is based in Los Angeles, Chicago, London, Paris and Sofia, your calculation will look in one of the following ways, depending on the database

Happy SQL-ing!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.