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
  • 278 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by McLaren
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 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?

http://ellislab.com/codeigniter/user-guide/helpers/date_helper.html

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 LastMitch

@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 LastMitch

@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 LastMitch 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.