Member Avatar for McLaren

Hello,

we are saving in the database time as our local time which is UTC+2

How to convert to other times?

I found in that this should work:

select sell_date,

 sell_date at time zone 'UTC+2' as UTC2
 FROM cards
order by id desc limit 1

This sql should return same time as it is, because its the same timezone.

But it is returing added 5 hours. Why could be that? Where to search the problem?

WHne I try

Select now()

It returns good time.

SELECT  current_setting('TIMEZONE')

returns 'localtime'

Edited by McLaren
  • 2 Contributors
  • 9 Replies
  • 303 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by McLaren

Dani AI

Generated

Quick diagnosis and where the symptom comes from: you stored wall‑clock (local) datetimes in a column that has no timezone, and then used the AT TIME ZONE operator with an offset string. PostgreSQL treats timestamp (without time zone) and timestamptz (with time zone) differently, and AT TIME ZONE does two opposite things depending on which type you feed it. Also, text offsets like UTC+2/+02:00 can be parsed in POSIX-style (signs can behave the opposite of ISO), so that literal can give the wrong direction unless you use an IANA zone name or an interval. (postgresql.org)

Practical checklist (fast verification)

  • Confirm the column type: run a metadata query against information_schema.columns to see whether your column is timestamp or timestamp with time zone.
  • If it is timestamp (no tz), PostgreSQL is treating those values as plain wall‑clock times and will not adjust them unless you explicitly tell it the source zone.

Example checks (replace names to match your schema):

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'your_table' AND column_name ILIKE '%time%';

How to fix (safe migration pattern)

  • Add a new timestamptz column, populate it by telling PostgreSQL what zone the old values actually represent, then swap the columns. For fixed +02 offsets use an INTERVAL; for DST-aware results use an IANA name.
ALTER TABLE your_table ADD COLUMN ts_utc timestamptz;

-- fixed offset (no DST):
UPDATE your_table SET ts_utc = my_local_ts AT TIME ZONE INTERVAL '+02:00';

-- DST-aware (recommended if values cross DST boundaries):
UPDATE your_table SET ts_utc = my_local_ts AT TIME ZONE 'Europe/Your_City';

-- then drop/rename columns as needed

Using AT TIME ZONE INTERVAL '... avoids POSIX-sign confusion for fixed offsets; using an IANA name (e.g. Europe/Your_City) gives correct DST handling. And use ALTER TABLE ... ALTER COLUMN TYPE ... USING ... if you prefer an in-place type change. (postgresql.org)

Notes and best practice

  • Prefer storing instants as timestamptz (the server records UTC and displays according to session TZ) so conversions and arithmetic behave correctly for charts and comparisons. If historic DST rules matter, always use full IANA names when you convert. Good debugging by and the pointer from helped narrow this to time‑zone naming and type semantics. (scribd.com)

Recommended Answers

All 9 Replies

Member Avatar for McLaren 
(sell_date::timestamp with time zone) at time zone 'UTC+2'

still is not giving rith result. Is missing 4 hours

sell_date at time zone 'UTC+2' as UTC2

BUt now I see it adds 4 hours instead of 5 in the above example. Not sure if I made mistake or what when thinking it add 5 hrs.

Member Avatar for Member #949455

@SPeed_FANat1c

BUt now I see it adds 4 hours instead of 5 in the above example. Not sure if I made mistake or what when thinking it add 5 hrs.

Instead of sell_date at time zone 'UTC+2' as UTC2

Try to used sell_date at time zone 'Europe/London'

Read add Time Zone Input:

http://www.postgresql.org/docs/current/interactive/datatype-datetime.html#AEN5714

Member Avatar for McLaren

Try to used sell_date at time zone 'Europe/London'

this adds 2 hours to the time. London is at zero offset. So 2 hour diference. Looks like it returns good time.

So I also tried

sell_date at time zone 'Europe/Vilnius' as vilnius

and it returns good time.

Also

sell_date at time zone 'EET' as EET,

returns good - same time.

So now the question why

sell_date at time zone 'UTC+2' as UTC2,

does not return corrent time?

ALso how will I convert the timezones from this?

I am saving in database values from that table, e.g. 'UM6'

Probablty only way is to have some hardcoded araray which maps those 'UM6' => X

But I have to find the correct value which posgre understands.

Thanks, at least some step forward :)

Edited by McLaren
Member Avatar for McLaren

BTW I guess by abreviatioin is incorrect to select.

I selected from this:

select * from pg_timezone_names

and there are timezones with same abbreviatsions but different time.

For example AST is having offset +3 hours at Asia/Qatar and same AST is having offset -4 hours at America/Puerto_Rico.

Thats interesting. So then I need to select by full name of zone.

Member Avatar for Member #949455

@SPeed_FANat1c

The link you provided is for CodeIgniter. Unless you created a code using it then you need to put it in the Framework or another words put in the folder of your destination.

I don't think it will work if you are using the code for something else it will only work on CodeIgniter

o then I need to select by full name of zone.

Correct

Member Avatar for McLaren

I am using codeigniter.

Member Avatar for Member #949455

@SPeed_FANat1c

I am using codeigniter.

OK, Then you really don't need to used Postgre time zone at all.

You know you never mention Codeigniter in the beginning.

What is your timezone?

To used Codeigniter you can do this:

$this->load->helper('date');

$now = time();

$timestamp = '1140153693';
$timezone = 'UTC+2';
$daylight_saving = TRUE;

echo gmt_to_local($now, $timestamp, $timezone, $daylight_saving);

There is a Timezone Reference that you can choose from.

Codeigniter has a different way of using date() function.

Edited by Member #949455 because: grammer
Member Avatar for McLaren

My timezone is UTC+2, Vilnius. Hmm, I already done using postgre. Need to think before understanding this if would work for my problem. But now there is no need I guess anymore. Unless there will be problems with daylight savings. In my select statements there is n consideration about daylight savings, but that could be not big problem, its not criticals spot.

I am just selecting items by time to display a chart.

Thanks :)

Edited by McLaren
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.