DATETIME(6) vs TIMESTAMP(6)

So just a quick reference shows that TIMESTAMP is almost identical, except that the first one in the row will automatically store the exact time that the row was made.  So you'd use TIMESTAMP primarily for audit purposes, such as createdOn or modifiedOn.

You could theoretically use DATETIME for that too, and for other things as well.  It appears there's no real functional advantage to TIMESTAMP unless you want to take advantage of the automatic time writing functionality.  I'd also take a look at how accurate Appian's dateTime type is, because you could be wasting bits of precision if you make your DB more precise than Appian.  The extra will just be truncated or rounded.

(Of course, assuming your post is in regard to the MariaDB DATETIME vs. TIMESTAMP)

mariadb.com/.../

DateTime - includes Date and Time, while TimeStamp also includes the Timezone. If you need timezone in your application, go with Timestamp, else DateTime should do.

Appian will manage timezones for you and store any datetime in UTC.

So this thread just helped me learn the genius of Appian's 2035 epoch.  It turns out, the 1970 epoch has a major flaw, in that dates post 2038 will overflow the integer and be set to dates in 1901.  I really like the nickname "Epochalypse". 

https://en.wikipedia.org/wiki/Year_2038_problem

Appian smart about how to avoid that.  Now if only all the datasources and other vendors they connect with also had elegant means of avoiding the same.

Hi Dave,

This is the actual reason why I'm looking for the difference.

Our legacy application is utilizing oracle DB and using timestamp(6) field, while when we are migrating the data to cloud Maria DB so as to decommission the old system and database we are getting error while writing the data in MariaDB.

There are multiple values which are set to 1970-01-01 00-00-00 000000 and they all are getting failed.

As a workaround I though to use Datetime(6) field but got to know that timestamp takes 4 byte while datetime takes 8 byte, so wanted to know if moving the data from timestamp(6) to datetime(6) will make any update to data or not and also if querying such data will have any impact on record/Query performance.

Maria DB Datetime can go to within a microsecond of 10,000 CE, and minimum value of 1/1/1000, 66 years before the battle of Hastings.  These are both further than the limits of Appian, which doesn't get you too far into the past beyond the Declaration of Independence, but it should be all you need for now.

mariadb.com/.../