PostgreSQL timestamp & timestampTz

PostgreSQL timestamp & timestampTz
Categories:
Tags:

Compare column type

Type Size Min value Max value Description
timestamp 8 bytes 4713 BC 294276 AD no timezone
timestampTz 8 bytes 4713 BC 294276 AD with timezone

Create timestampTz time data

Instead of just using Carbon Object or Y-m-d H:i:s format time data to timestampTz. You should add timezone info at your time data when you create time data into the database, such as Y-m-d H:i:sP format time data. Otherwise the default timezone will be GMT+0 without timezone info.

You can specify the timezone information in your date to get the correct time range that you want.

Type Data
Carbon Object 2020-12-25 14:31:00
time format Y-m-d H:i:s 2020-12-25 14:31:00
time format Y-m-d H:i:sP 2020-12-25 14:31:00+08:00 
// Transforom time to Carbon Object
$TimeAtCarbon = Carbon::parse($time_at);

// Set create data
$timezone_data = [
    'time_at_timezone' => $TimeAtCarbon->format('Y-m-d H:i:sP'),
];

// Insert data to Database
TimezoneTest::create($timezone_data);

You can use $TimeAtCarbon->toDateTimeString() to get time, and $TimeAtCarbon->getTimezone()->toOffsetTimeZone() to get timezone. It makes your code more readable.

// Transforom time to Carbon Object
$TimeAtCarbon = Carbon::parse($time_at);

// Set create data
$timezone_data = [
    'time_at_timezone' => $TimeAtCarbon->toDateTimeString() . $TimeAtCarbon->getTimezone()->toOffsetTimeZone(),
];

// Insert data to Database
TimezoneTest::create($timezone_data);

The default timestampTz data format will be GMT+0, so if you save 2020-12-30 14:31:00+08:00 with GMT+8 format to your database. Then it will store as 2020-12-30 06:31:00+00:00.

Select timestampTz data

You have to specify the correct timezone in your time select conditions. Otherwise, the PostgreSQL will use GMT+0 to get your timestampTz data while selection.

When you specify the correct timezone data that you want. No matter what timezone that you want. You will always get the correct data.

So the 2020-12-30 14:31:00+08:00 and 2020-12-30 06:31:00+00:00 will get the some result.

// Time setting
$time_start_at = '2020-12-30 14:31:00+08:00'; // 2020-12-30 06:31:00+00:00
$time_end_at = '2021-01-01 14:31:00+08:00';   // 2021-01-01 06:31:00+00:00
// Transform time to Carbon Object
$TimeStartAtCarbon = Carbon::parse($time_start_at);
$TimeEndAtCarbon = Carbon::parse($time_end_at);

// Setting time with timezone
// 2020-12-30 14:31:00+08:00
$time_start_at_timezone = $TimeStartAtCarbon->toDateTimeString() . $TimeStartAtCarbon->getTimezone()->toOffsetTimeZone();
// 2020-01-01 14:31:00+08:00
$time_end_at_timezone = $TimeEndAtCarbon->toDateTimeString() . $TimeEndAtCarbon->getTimezone()->toOffsetTimeZone();

// select * from "timezone_test" where "time_at_timezone" >= '2020-12-30 14:31:00+08:00' and "time_at_timezone" <= '2020-01-01 14:31:00+08:00'
$TimezoneTestCollection = TimezoneTest::where('time_at_timezone', '>=', $time_start_at_timezone)
    ->where('time_at_timezone', '<=', $time_end_at_timezone)
    ->get();

Using Carbon to transform your timezone

The official documentation told us to use setTimezone('UTC') or utc() to transform time to UTC format. But it will go wrong when I change my system time from UTC to Asia/Taipei.

// Transform to UTC
$CarbonTime->setTimezone('UTC');
$CarbonTime->utc();

So it’s highly recommended you use setTimezone('GMT+0') to change your timezone format directly. Then the result of the transform will be correct.

$CarbonTime->setTimezone('GMT+0');

Transform timestamp format to timestampTz

The timestamp format will not include timezone information. The timezone date will be decided by your system or your code.

So when your save the timestampTz format data, 2020-12-25 14:31:00+08:00, to timestamp format. The data storage will just 2020-12-25 14:31:00 and not include timezone information. So the timezone it will depends on your system or your code.

Type Data
timestampTz 2020-12-25 14:31:00+08:00
timestamp 2020-12-25 14:31:00

timestampTz feature

  • You can specify the timezone that you want to get the correct time range of the data
    • Taipei Times: 2020-12-25 14:31:00+08:00
    • Bangkok Times: 2020-12-25 14:31:00+07:00
  • Timezone information will be in the database not depends on the system or code.
    • Using timestamp without timezone will be a disaster while you want to change your timezone from your system or code.
  • You still can show the correct time in right timezone

Reference