I’ve been trying to do a fairly simple thing – selecting a series of times of day (e.g. 7:00 p.m.) from a dropdown.
In the processing of that data, I’m getting to substantial issues with a) dates being stored despite it being “time only” and b) processing to remove those dates resulting in bizarre rounding. So looking for help on how to avoid this in implementation.
Here’s a summary of what I’m seeing:
If you have a dropdown column of values like “7:30 PM”, when that option is selected via an input form, it seems to store 7:30 PM + a date value. I can find no way to not have it store a time value only, even though the column type is “time only”
If you do math like time - trunc(time) – to remove the date value, I sometimes get a shift of exactly 5 minutes. Other days there’s no shift. I assume it’s some bizarre rounding in the data storage layer?
aside from that another thing to try out is using a format date column (using Luxon) with “T”
or check other formats here luxon - Immutable date wrapper
First of all, there is no such thing as a time only column. If you are referring to a date column, then that is designed to hold both a date and a time with the option to adjust the visible formatting to be date, time, or both. Underneath it’s still a full date and time.
Are you trying to merge that time with a date? Are you trying to do any date math with it? How is it ultimately being used?
A couple initial thoughts would be to just store it in a text column. Or change the table that your choice component is sourced from to show the time but write the decimal equivalent of that time to the table, which makes date math a lot easier. There’s a few different approaches, but it depends on how you intend to use that time value everywhere else in your app.
Two time columns are being used to calculate a time difference every day (work started - work ended) – and then graph it as well (start time by day / end time by day)
I think what I would do is figure out the decimal equivalent of each time. For example, 7am = 0.29166 and 5pm = 0.70833. The different is 0.41667. Those decimal values would be what your choice component writes to the table, but you would still display the friendly version of those times.
Doing the math like this would give you a 10 hour difference between 7am and 5pm
