CODEWITHSUNDEEP
postgresql
fisch
fisch

Reputation: 703

Adjust a timestamp across weekdays

I have a table in Postgres with weekdays and a time (hours in 24h mode). Like:

weekday | time
---------------
sun     | 04
wed     | 23

Now I need those times 5 hours earlier. How do I get to:

weekday | time
---------------
sat     | 23
wed     | 18

Upvotes: 1

Views: 62

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51609

another not less ugly way:

t=# with a as (
  with
    i as (select 5 h)
  , a as (
    select to_char(g, 'dy'),g delta
    from generate_series('2017.05.15','2017.05.21','1 day'::interval) g
  )
  select s145.*,delta + (time||' hours')::interval - concat(h,' hours')::interval nt
  from s145
  join i on true
  join a on a.to_char = weekday
)
select to_char(nt, 'dy'), to_char(nt,'HH24')
from a;
 to_char | to_char
---------+---------
 sat     | 23
 wed     | 18
(2 rows)

and for eg 2 hours:

t=# with a as (
  with
    i as (select 2 h)
  , a as (
    select to_char(g, 'dy'),g delta
    from generate_series('2017.05.15','2017.05.21','1 day'::interval) g
  )
  select s145.*,delta + (time||' hours')::interval - concat(h,' hours')::interval nt
  from s145
  join i on true
  join a on a.to_char = weekday
)
select to_char(nt, 'dy'), to_char(nt,'HH24')
from a;
 to_char | to_char
---------+---------
 sun     | 02
 wed     | 21
(2 rows)

I took '2017.05.15' because I know it is last monday. It will respect time switch that probably not what the op wants. of course it is easily avoidabe with set timezone to 'utc'. But I don't want to complicate already ugly code even more

Upvotes: 2

JNevill
JNevill

Reputation: 50209

You can use a CASE statement to get the results you desire. It just gets a little ugly, but it's not unmanageable.

SELECT
    CASE WHEN time - 5 < 0 THEN
        CASE 
            WHEN weekday = 'Sun' THEN 'Sat'
            WHEN weekday = 'Sat' THEN 'Fri'
            WHEN weekday = 'Fri' THEN 'Thu'
            WHEN weekday = 'Thu' THEN 'Wed'
            WHEN weekday = 'Wed' THEN 'Tue'
            WHEN weekday = 'Tue' THEN 'Mon'
            WHEN weekday = 'Mon' THEN 'Sun' 
            END
        ELSE weekday
        END as weekday,
    CASE WHEN time - 5 < 0 THEN 24 - (time-5) ELSE time-5 END as time
FROM yourtable;

You could use a CTE so that you only have to monkey with changing that "5" in one spot too. Also adding the ability to put a positive or negative number and handle rolling forward a day:

WITH timeshift AS
(
    SELECT -5 as hours
)
SELECT
    CASE 
        WHEN time + timeshift.hours < 0 THEN
            CASE 
                WHEN weekday = 'Sun' THEN 'Sat'
                WHEN weekday = 'Sat' THEN 'Fri'
                WHEN weekday = 'Fri' THEN 'Thu'
                WHEN weekday = 'Thu' THEN 'Wed'
                WHEN weekday = 'Wed' THEN 'Tue'
                WHEN weekday = 'Tue' THEN 'Mon'
                WHEN weekday = 'Mon' THEN 'Sun' 
                END
        WHEN time + timeshift.hours >= 24 THEN
            CASE 
                WHEN weekday = 'Sat' THEN 'Sun'
                WHEN weekday = 'Sun' THEN 'Mon'
                WHEN weekday = 'Mon' THEN 'Tue'
                WHEN weekday = 'Tue' THEN 'Wed'
                WHEN weekday = 'Wed' THEN 'Thu'
                WHEN weekday = 'Thu' THEN 'Fri'
                WHEN weekday = 'Fri' THEN 'Sat'
                END
        ELSE weekday
        END as weekday,
    CASE 
        WHEN time + timeshift.hours < 0 THEN 24 - (time + timeshift.hours) 
        WHEN time + timeshift.hours >= 24 THEN (time + timeshift.hours) - 24
        ELSE time + timeshift.hours 
        END as time
FROM yourtable, timeshift

Upvotes: 2

Related Questions