GMT time to Pacific time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GMT time to Pacific time

Author

Topic

havish
Starting Member

1 Post

Posted - 2012-05-17 : 23:32:41

Hello,

I have this sql query which outputs user logged activity for each hour, like at 00hrs, they were 200 people logged in.. etc.. BUT the problem is it shows in GMT time and i need help displaying the same in US/Pacific time..

thank you

select hour_GMT, avg(user_count)
from
(
select
to_char(ps.session_create_time, 'DAY') as day_of_week,
to_char(ps.session_create_time, 'mm/dd/yyyy') as day_in_GMT,
to_char(ps.session_create_time, 'HH24') as hour_GMT,
count(distinct p.user_id) as user_count
from storagedrop.user p
inner join storagedrop.user_session ps on p.user_id = ps.user_id
where ps.session_create_time - 7/24 > to_date('APR 30, 2012','MON DD, YYYY')
group by to_char(ps.session_create_time, 'mm/dd/yyyy'), to_char(ps.session_create_time, 'DAY'), to_char(ps.session_create_time, 'HH24')
order by to_char(ps.session_create_time, 'mm/dd/yyyy'), to_char(ps.session_create_time, 'DAY'), to_char(ps.session_create_time, 'HH24')
)
where day_of_week not in ('SATURDAY','SUNDAY')
group by hour_of_day_in_GMT
order by hour_of_day_in_GMT;

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2012-05-18 : 06:41:55

Try something like this:


select RIGHT('00' + CAST((CAST(LEFT(hour_GMT, 2) AS INT) + 8) % 24 AS VARCHAR(2)), 2) + 'hrs', avg(user_count) 
....

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-18 : 11:25:52

For a given date do you know what the correct offset is to convert from GMT to PST/PDT?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources