Currently we use timestamp in db2 and Memsql supports datetime and timestamp. Noticed timestamp has limitations to take date between 1969 to 2038 and datetime cannot be initialized or defaulted in DDL. I have confusion which one is best fit for us.
Pleas suggest.
TIMESTAMP is stored internally as a unix timestamp and converted to the servers timezone when returned to the user (so if the servers timezone is modified the results of queries over TIMESTAMP columns will change). DATETIME just stores the date given to it and returns it unmodified in queries no matter the servers timezone. I would think DATETIME is closer to what a db2 TIMESTAMP is (except DATETIME in MemSQL doesn’t support specifying a timezone like TIMESTAMP in db2 does).
Its a missing feature that we don’t support DATETIME types with DEFAULT NOW()\ON UPDATE NOW(). It should be added one of the next few releases.