1,105,644 Community Members

Postgre time zone problems

Member Avatar
SPeed_FANat1c
Posting Pro
514 posts since Apr 2010
Reputation Points: 3 [?]
Q&As Helped to Solve: 18 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Deleted Member
 
0
 

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

Member Avatar
SPeed_FANat1c
Posting Pro
514 posts since Apr 2010
Reputation Points: 3 [?]
Q&As Helped to Solve: 18 [?]
Skill Endorsements: 0 [?]
 
0
 
(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
Deleted Member
 
1
 

@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
SPeed_FANat1c
Posting Pro
514 posts since Apr 2010
Reputation Points: 3 [?]
Q&As Helped to Solve: 18 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
SPeed_FANat1c
Posting Pro
514 posts since Apr 2010
Reputation Points: 3 [?]
Q&As Helped to Solve: 18 [?]
Skill Endorsements: 0 [?]
 
0
 

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
Deleted Member
 
0
 

@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
SPeed_FANat1c
Posting Pro
514 posts since Apr 2010
Reputation Points: 3 [?]
Q&As Helped to Solve: 18 [?]
Skill Endorsements: 0 [?]
 
0
 

I am using codeigniter.

LastMitch
Deleted Member
 
0
 

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

Member Avatar
SPeed_FANat1c
Posting Pro
514 posts since Apr 2010
Reputation Points: 3 [?]
Q&As Helped to Solve: 18 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Question Answered as of 1 Year Ago by LastMitch
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article