Timestamp_minute_of_day

@michael,

In queries, I can extract loads of helpful info from a timestamp. I just came across a circumstance where I need a piece of info that doesn’t seem to be in the list. Would it be possible to add timestamp_minute_of_day?

For example, 10am today would be 600 (600 minutes since midnight).

Hi Erin, I can see how that might be handy. We will take a look at this and I’ll let you know.

1 Like

Side note: I thought I’d try to get around this by getting the hour, multiplying it by 60, and adding the minutes. However, when I go to add the minutes, I’m not able to do any manipulation on the input. (I need to select the timestamp and use the “timestamp_minute” filter.) There are a number of places in my query where being able to manipulate these inputs would be a big deal.

So either of those solutions (“timestamp_minute_of_day” or manipulating query filter inputs) would work for me.

Shoot. I think I need timestamp_day_of_week, too.

Extra shoot. And I think it has to be

M=0
T=1
W=2
Th=3
F=4
Sa=5
Su=6

That feels like a tall order though…

There’s no chance we could get to_timestamp as a query filter, is there?

Hi Erin,

timestamp_day_of_week is a filter, it starts with Sunday=0 though. Why do you need it to start at Monday = 0? One thought of a work around is just to subtract 1, after the filter:

For example, this gets me records created on Wednesday, where Wednesday = 2

I was able to do the calculations described above, but I might not be understanding fully so please clarify if I am. I recorded this video to show you what I did. Please let me know if I’m missing something

Oh! How did I totally overlook that?! (timestamp_day_of_week). Thanks, and brilliant on the subtracting 1. However, if it returns 0 (Sunday) and I subtract 1, it would give me -1 instead of 6, wouldn’t it? (Because the subtraction happens outside of the timestamp context.)

I need it to be Monday=0 because that’s what Yelp–my data source–uses. If there are no other ways around it, maybe I could convert it when I store the data in my system, but I’m pretty sure that would affect a number of things throughout the app (how I display data for a weekend vs weekday, etc. It’s really nice to have the weekend grouped together rather than split between 6 and 0.)

Regarding to_timestamp in queries, I believe I found a workaround. I haven’t finished building out the whole query yet so I’m not 100% sure it’s working correctly, but that might not be a dire need anymore.

@michael, I just found a way around needing my day_of_week starting on Mon=0. I’m using a calendar table, and I simply added day_of_week to that so I can reference it dynamically rather than needing to calculate it in the query. Thank you for the back-and-forth! It helped get my brain going.

So we’re back to needing either (1) timestamp_minute_of_day, or (2) be able to apply additional filters to the values within query filters.

Good point about subtracting 1 from Sunday at = 0. And that makes a lot of sense to put a day of the week field from a calendar table to reference. Glad you figured out that workaround.

I might be missing something for what you mean for (2) to apply additional filters to the values - did you see the video I made above?

My use case is super complex, but I’ll try to create a simplified version to show what I mean about applying additional filters:

  1. A store is open every Friday from 10 a.m. to 10 p.m.

  2. In my records, I record that as
    “week_day”: 5,
    “minute_start”: 600,
    “duration_in_minutes”: 720

  3. A user is available on Friday, May 21st at 10:30 a.m.

  4. I need to know if the store is open when the user is available.

  5. With the user’s available timestamp in hand, I would like to simply apply timestamp_minute_of_day and see if that number (630) is between 600 and 1320 (minute_start + duration_in_minutes).

  6. Since I don’t have timestamp_minute_of_day, I’m trying to calculate that number by:
    -Getting timestamp_hour
    -Multiplying it by 60
    -Adding timestamp_minute

image

But I get stuck on that last bit: adding timestamp_minute. That’s because I can add. Or I can get timestamp_minute. But I cannot add timestamp_minute because that would be a filter within a filter…

Does that make more sense?