AM/PM question

[Rich Shepard]

A couple of sqlalchemy classes need to store user-entered times. The
intended user audience is probably unfamiliar with using a 24-hour clock so
I'll use the ttk.Radiobutton to have the user select AM or PM for the period.

How do I represent this variable in the class definition of columns? The
widget returns a boolean True/False for both choices, AM and PM so do I use
a String containing 'AM' or 'PM'? Are there examples for this available?

Hope you're all staying healthy,

Rich

[Jonathan Vanasco]

The common approach to this situation is storing the data as a 24hour timestamp in the database/sqlalchemy and converting it to a 12hour am/pm for display/editing.

You could use a "12 hour" option and an enum column for am/pm or a string. You're going to have a much easier time in the longterm by using a standard timestamp though.

[Rich Shepard]

Jonathan,

I understand this working if the time to be entered is the current time, but
not if the time is different. For example, a sample collection time and its
analysis times will be different and, in my application, entered well after
they occured. That's why I'm asking for guidance.

For example, a sample could be taken on 1 June at 11:00 hours, transported
to the analytical laboratory where it was analyzed on 2 June at 13:50 hours.
The report is sent to the user on 10 June and those times are entered in the
application any time after that.

Regards,

Rich

[Jonathan Vanasco]

It doesn't matter when the time is entered or what it is supposed to reflect.  The best option -- by a wide margin -- for storing any time values is in a TIMESTAMP column, and for storing date+time values is in a DATETIME column.  These types of columns/fields/formats exist to streamline data storage and leverage numerous operations on the database and in your application.

Even though you will only show the user an option of 12hours and am/pm, on the backend you should be storing the time in a 24hour native time object; if there is a date associated, you should be storing it in a timestamp.  That will allow you to easily query records with these fields in the future.

Timestamp/Datetime:
   * Python https://docs.python.org/3/library/datetime.html#datetime-objects 
   * SqlAlchemy https://docs.sqlalchemy.org/en/13/core/type_basics.html?highlight=timestamp#sqlalchemy.types.DateTime

Time:

   * Python - https://docs.python.org/3/library/datetime.html#time-objects
   * SqlAlchemy Column - https://docs.sqlalchemy.org/en/13/core/type_basics.html?highlight=timestamp#sqlalchemy.types.Time

If you feel a strong need to handle this otherwise, you can use an Enum (https://docs.sqlalchemy.org/en/13/core/type_basics.html?highlight=timestamp#sqlalchemy.types.Enum) column to constrain the am/pm options.  You can also just use a string column and enforce the selection with constraints.

Your best path forward, however, is convert the user-input into a python Time or DateTime object and store that object in the database; then you can construct the widgets for "display/edit" by setting their defaults to be the day/month/year/hour/minute of the datetime field in your sqlalchemy object.

[Rich Shepard]

On Fri, 20 Mar 2020, Jonathan Vanasco wrote:

> It doesn't matter when the time is entered or what it is supposed to
> reflect. The best option -- by a wide margin -- for storing any time

> values is in a TIMESTAMP column ...

Jonathan,

Thanks. I've not before needed this information and I appreciate your
providing the insight I need.

Best regards,

Rich