Another option would be to have separate columns for date and time,
and leave the time column NULL when it's not present.
If you still want to store it as a string, I guess you'll need to try
parsing it as a datetime and then fall back to parsing it as a date.
You haven't said what database you are using, but for MySQL it might
be something like this:
IFNULL(STR_TO_DATE(timepoint, <your-datetime-format>),
STR_TO_DATE(timepoint, <your-date-format>))
If you're not actually going to filter on these columns, I don't think
I'd do any of this at all. Instead, I'd use something like a
TypeDecorator to convert the values when loading from and saving to
the database.
Simon
On Fri, Sep 16, 2016 at 9:53 AM, Jinghui Niu <niujing...@gmail.com> wrote:
> If I store the as DateTime values and with a second column to indicate
> whether it's a date or datetime, it would look like this for a Date:
> col1: "2016-09-16 00:00:00", col2: "date only"
>
> It looks so messy to me:)
>
> I'd prefer to have Date and DateTime distinctively written in my database. I
> admit that it's one of my personal preference though:)
>
> On Fri, Sep 16, 2016 at 1:49 AM, Jinghui Niu <niujing...@gmail.com> wrote:
>>
>> Thanks for reply.
>>
>> The reason is simple. I plan in the future to accommodate datetime range
>> into that column as well, so storing this logic as plain strings gives the
>> most flexibility. This is a project that I'm learning by making. So I would
>> like to try all the new features for later tasks.
>>
>> I'm fascinated by hybrid_property, but is stuck now when trying to
>> dispatch functions at the SQL expression level.
>>
>> Jinghui
>>
>> On Fri, Sep 16, 2016 at 1:34 AM, Simon King <si...@simonking.org.uk>
>> wrote:
>>>
>>> Ignore SQLAlchemy for the moment and describe what you are trying to
>>> achieve. It looks like you want to store dates and times as strings in
>>> your database (rather than the appropriate type), and yet still be
>>> able to perform date-related operations on them qhen querying. Is that
>>> right? Is there a reason why you can't store them as proper DateTime
>>> values (perhaps with a second column to indicate whether or not the
>>> time part is valid)?
>>>
>>> Simon
>>>
>>> On Fri, Sep 16, 2016 at 4:39 AM, Jinghui Niu <niujing...@gmail.com>
>>> wrote:
>>> > I have the following code snippet, I marked my question in a comment
>>> > line
>>> > inside the hybrid_property.expression part. As you can see, it is now
>>> > not
>>> > implemented:
>>> >
>>> > from sqlalchemy.ext.declarative import declared_attr
>>> > from sqlalchemy import Column, Integer, String, Unicode, UnicodeText
>>> > from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method
>>> > import arrow
>>> >
>>> >
>>> > datetimeString_format = {
>>> > "UTC": "%Y-%m-%d %H:%M:%S+00:00",
>>> > "local_with_timezoneMarker": "%Y-%m-%d %H:%M:%S %Z",
>>> > "local_without_timezoneMarker": "%Y-%m-%d %H:%M:%S"
>>> > }
>>> >
>>> >
>>> > dateString_format = "%Y-%m-%d"
>>> >
>>> >
>>> > class My_TimePoint_Mixin:
>>> > # define output formats:
>>> > datetimeString_inUTC_format = "%Y-%m-%d %H:%M:%S+00:00"
>>> > datetimeString_naive_format = "%Y-%m-%d %H:%M:%S"
>>> >
>>> >
>>> > # instrumented fields:
>>> > _TimePoint_in_database = Column('timepoint', String,
>>> > nullable=False)
>>> > _TimePoint_in_database_suffix = Column(
>>> > 'timepoint_suffix', String, nullable=False)
>>> >
>>> >
>>> > @hybrid_property
>>> > def timepoint(self):
>>> > twoPossibleType_handlers = [
>>> > self._report_ACCRT_DATE,
>>> > self._report_ACCRT_DATETIME
>>> > ]
>>> > for handler in twoPossibleType_handlers:
>>> > print("handler: ", handler)
>>> > try:
>>> > return handler(self)
>>> > except (AssertionError, ValueError) as e:
>>> > logging.warning("Try next handler!")
>>> >
>>> > @timepoint.expression
>>> > def timepoint(cls):
>>> > pass
>>> > # How can I implement SQL expression for a equivalent function
>>> > dispatch here? There seems to be no SQL equivalent structure for this?
>>> >
>>> >
>>> > @timepoint.setter
>>> > def timepoint(self, datetimepointOBJ):
>>> > handlers_lookup = {
>>> > datetime.datetime: self._set_ACCRT_DATETIME,
>>> > datetime.date: self._set_ACCRT_DATE
>>> > }
>>> > this_time = type(datetimepointOBJ)
>>> > this_handler = handlers_lookup[this_time]
>>> > print("handler: ", this_handler)
>>> > this_handler(datetimepointOBJ)
>>> >
>>> >
>>> > def _report_ACCRT_DATE(self):
>>> > """Accurate Date"""
>>> > assert self._TimePoint_in_database_suffix == "ACCRT_DATE"
>>> > date_string = self._TimePoint_in_database
>>> > dateString_format = "%Y-%m-%d"
>>> > # return a datetime.date
>>> > return datetime.datetime.strptime(date_string,
>>> > dateString_format).date()
>>> >
>>> >
>>> > def _report_ACCRT_DATETIME(self):
>>> > """Accurate DateTime"""
>>> > assert self._TimePoint_in_database_suffix in
>>> > pytz.all_timezones_set
>>> > datetimeString_inUTC = self._TimePoint_in_database
>>> > utc_naive = datetime.datetime.strptime(
>>> > datetimeString_inUTC, self.datetimeString_inUTC_format)
>>> > utc_timepoint = arrow.get(utc_naive, "utc")
>>> > # localize
>>> > local_timepoint =
>>> > utc_timepoint.to(self._TimePoint_in_database_suffix)
>>> > # return a datetime.datetime
>>> > return local_timepoint.datetime
>>> >
>>> >
>>> > def _set_ACCRT_DATETIME(self, datetimeOBJ_aware):
>>> > assert isinstance(datetimeOBJ_aware, datetime.datetime), "Must
>>> > be a
>>> > valid datetime.datetime!"
>>> > assert datetimeOBJ_aware.tzinfo is not None, "Must contain
>>> > tzinfo!"
>>> > utctime_aware_arrow = arrow.get(datetimeOBJ_aware).to('utc')
>>> > utctime_aware_datetime = utctime_aware_arrow.datetime
>>> > store_datetime_string = utctime_aware_datetime.strftime(
>>> > self.datetimeString_inUTC_format)
>>> > self._TimePoint_in_database = store_datetime_string
>>> >
>>> >
>>> > def _set_ACCRT_DATE(self, dateOBJ):
>>> > store_date_string = dateOBJ.isoformat()
>>> > self._TimePoint_in_database = store_date_string
>>> >
>>> > Could someone please point out a clue as to implement the
>>> > hybrid_property.expression part? Thanks.
>>> >
>>> > I've also posted a similar issue on stackoverflow.com, for reference:
>>> >
>>> >
>>> > http://stackoverflow.com/questions/39485440/calling-from-the-same-class-why-is-one-treated-as-bound-method-while-the-other
>>> >
>>> > --
>>> > You received this message because you are subscribed to the Google
>>> > Groups
>>> > "sqlalchemy" group.
>>> > To unsubscribe from this group and stop receiving emails from it, send
>>> > an
>>> > email to sqlalchemy+unsubscr...@googlegroups.com.
>>> > To post to this group, send email to sqlalchemy@googlegroups.com.
>>> > Visit this group at https://groups.google.com/group/sqlalchemy.
>>> > For more options, visit https://groups.google.com/d/optout.
>>>
>>> --
>>> You received this message because you are subscribed to a topic in the
>>> Google Groups "sqlalchemy" group.
>>> To unsubscribe from this topic, visit
>>> https://groups.google.com/d/topic/sqlalchemy/i8k2u7bK5x4/unsubscribe.
>>> To unsubscribe from this group and all its topics, send an email to
>>> sqlalchemy+unsubscr...@googlegroups.com.
>>> To post to this group, send email to sqlalchemy@googlegroups.com.
>>> Visit this group at https://groups.google.com/group/sqlalchemy.
>>> For more options, visit https://groups.google.com/d/optout.
>>
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
