How to Know or Alter the Timezone of a Timestamp Column in a View/Table?

Question

To know about the data type of a column, I am running this query. 
```sql
SELECT 
  *
FROM 
  information_schema.columns
WHERE 
  table_name = 'orders'
  AND column_name = 'shipment_time';
```
and it tells me the data type of ```shipment_time``` is ```timestamp(3)```.  But I am unable to gain any information on the timezone.

For context, I am in Singapore, and expect the timezones to be UTC+8. But when I convert the timestamp to unix epoch using the ```to_unixtime``` built in function, and check the epoch converter website, it seems to give me UTC timezone.  To make it clear, suppose

```shipment_time```=11.35 pm on the table

```to_unixtime``` gives me the epoch, suppose 12345678 (hypothetically).

I put 12345678 on epoch converter.

It tells me the GMT time is 11.35 pm, and Singapore time is 7.35 am next day.

So, can I trust epoch converter? If yes, then there is an eight hour offset. That is why I am checking if athena itself is timezone aware, and it stores the zone information as part of the schema anywhere. If so, how can I adjust it to reflect Singapore time for each shipment? 
Note that the athena itself is running in AP-Southeast-1 zone.

Answer

In Athena, the TIMESTAMP data type is used to represent points in time, but it's important to note that it doesn't inherently include timezone information. By default, Athena treats all timestamps as UTC. If you need to display or work with the data in a different timezone, such as Singapore Time (UTC+8), you have to explicitly convert the timestamps in your queries.

You can use the AT TIME ZONE clause in SQL queries to convert timestamps to a specific timezone. For example, to convert a timestamp column to Singapore Time, you would use a query similar to:

```
SELECT shipment_time AT TIME ZONE 'Asia/Singapore' as local_shipment_time FROM orders;
```
This query will convert the shipment_time from UTC to Singapore Time.

Furthermore, the Redshift CONVERT_TIMEZONE function can be used for converting a timestamp from one timezone to another. This function can be particularly useful if you're dealing with data warehousing scenarios where you need to convert timestamps between different timezones. The syntax for using this function is:
```
CONVERT_TIMEZONE('source_timezone', 'target_timezone', 'timestamp')
```
For instance, to convert from UTC to Singapore Time, you might use:

```
SELECT CONVERT_TIMEZONE('UTC', 'Asia/Singapore', shipment_time) FROM orders;

```
For more information, you can refer to [Redshift CONVERT_TIMEZONE function](https://docs.aws.amazon.com/redshift/latest/dg/CONVERT_TIMEZONE.html).

Let me know if you have any further questions.

Regards!

Mina

mina@optimecloud.com

How to Know or Alter the Timezone of a Timestamp Column in a View/Table?

関連するコンテンツ