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: Logo

You are commenting using your 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.