Optimization: How to get TimeId from time for each minute in a week?

Question

I am creating a table which will have 2 columns:

1. Day_time (time from 1978-01-01 00:00:00 Sunday, till 1978-01-07 23:59:00.0 Saturday, Granularity: Minute)
2. Time_id (a unique id for each minute), to be populated

I have column one populated. I want to populate column two.

How I am doing it right now:

EXTRACT(dayofweek FROM day_time) * 10000 + DATEDIFF('minutes', TRUNC(day_time), day_time)

I basically want a function where I pass any date and it tells me where I am in a week. So, I need a function, just like the function above. Just more optimized, where I give a date and get a unique ID. The unique ID should repeat weekly.

Example: ID for Jan 1, 2015 00:00:00 will be same as Jan 8, 2015 00:00:00.

Why 1978-01-01? cuz it starts from a Sunday.    
Why 10,000? cuz the number of minutes in a day are in four digits.

Answer 1

You can do it all in one fell swoop, without needing to extract the date separately:

SELECT DATEDIFF('minutes', date_trunc('week',day_time), day_time) which I'd expect to be marginally faster.

Another approach that I'd expect to be significantly faster would be converting the timestamp to epoch, dividing by 60 to get minutes from epoch and then taking the value modulus of 10,080 (for 60 * 24 * 7 minutes in a week).

SELECT (extract(epoch from day_time) / 60) % 10080

If you don't care about the size of the weekly index, you could also do:

SELECT (extract(epoch from day_time)) % 604800 and skip the division step altogether, which should make it faster still.