Postgre time zone problems

[SPeed_FANat1c]

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'

[LastMitch]

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

Since your local time is UTC+2 and it 5 hours ahead.

Try to used TIMESTAMP WITH TIME ZONE:

http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm

[SPeed_FANat1c]

(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.

[LastMitch]

@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

[SPeed_FANat1c]

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 :)

[SPeed_FANat1c]

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.

[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

[SPeed_FANat1c]

I am using codeigniter.

[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.

[SPeed_FANat1c]

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 :)