golang converting time.Time to unix adds extra hours

[arjunsri (arjun)]

I ran into a problem. The PostgreSQL database stores the date and time in the format “timestamp without time zone”. If you get the date and time from the database and try to convert it to seconds it adds 4 extra hours Example:

fmt.Println(menu.OpeningRecordAt, "|", time.Unix(menu.OpeningRecordAt.Unix(), 0))
// 2023-05-21 00:00:00 +0000 UTC | 2023-05-21 04:00:00 +0400 +04

What are some ways to fix this or convert the date and time seconds more correctly?

I tried changing the data type in postgres to timestamp with time zone but the conversion also adds extra hours

[Dean_Davidson]

Those are the same timestamps. One is UTC and one is local time (+0400 in your case). If you’re storing timestamps as UTC, it would be correct to add 4 hours to it to get local time.

[freeformz (Edward Muller)]

To add to what @Dean_Davidson said above … add .UTC() to the one on the right or .Local() to the one of the left.

PS: Always use UTC with times if you can.

[bluefire]

The issue you’re facing is related to time zone conversion when retrieving data from the PostgreSQL database. To handle this correctly, you need to consider the time zone information associated with the timestamps and convert them appropriately. Here are a few approaches to fix or convert the date and time accurately:

1. Specify Time Zone: Ensure that you set the appropriate time zone when connecting to the PostgreSQL database. You can specify the time zone in the connection string or using the SET TIME ZONE command after connecting. This ensures that the retrieved timestamps are adjusted according to the desired time zone.
2. Use Time Zone Functions: PostgreSQL provides various time zone-related functions that allow you to manipulate and convert timestamps based on specific time zones. Functions like AT TIME ZONE and timezone() can be used to convert timestamps to a particular time zone or adjust them based on a specific offset.
3. Handle Time Zone Conversion in Go: Instead of relying on PostgreSQL to handle the time zone conversion, you can retrieve the timestamps as-is and perform the conversion in Go. You can use the time.Time type’s In() method to convert the time to a specific time zone. Here’s an example:

// Assuming menu.OpeningRecordAt is of type time.Time
loc, _ := time.LoadLocation("YourDesiredTimeZone")
convertedTime := menu.OpeningRecordAt.In(loc)

Replace "YourDesiredTimeZone" with the time zone you want to convert the timestamp to. This approach gives you more control over the time zone conversion within your Go code.
4. Normalize Time Zone Handling: If your application deals with multiple time zones, it’s essential to establish a consistent approach to handle time zones throughout your application. Normalize all timestamps to a single time zone (e.g., UTC) when storing and retrieving them from the database. Perform any necessary time zone conversions explicitly based on user preferences or requirements.

By following these approaches, you can ensure that the date and time conversions are performed correctly, accounting for the time zone differences between the PostgreSQL database and your application.