Best practice for Datefield?

Hello short question: Whats the best and comfortable way to work with Date fields? Set datefield in Mysql DB or Timestamp? Or should i use another field? Unixtime is nice for knowing when a tablerow was inserte or not?

Why I‘m questioning is because here in Europe we use different timezone and we use DDMMYYYY instead other format in US or similar.

And later for working with Jquery/Bootstrap Datepicker how to use it properly for not converting these Dateformat forward and backwards again to strugge with it?

And bonus question: How to work with Timezone if a portal is working in multiple countries with different timezones? :thinking:

Hi Freddy,
In your database it is always best to store the date in an ISO format, i.e. what the default mysql date field format is: “yyyy-mm-dd”. Then on the front end you can apply whatever format you need.

1 Like

Okay means in Mysql i use that field with the format you gave me? So nothing to change in mysql and set that just as „Date“ field. And in Frontend i use the proper format which matches for example Europe date? So no handling or change on backend side right?

Yes, use the default “DATE” or “DATETIME” field in your mysql database.
On the front-end use the available date formats to format your date.

Okay great! Buuuuuuuuut: If i want to know when a db record was created i use that {{NOW}} thing or should I handle that with unix timestamp so DB Server handles the date/time of record by its own?

Solution1: PHP Side with {{NOW}}


Solution2: MySQL side via UnixTimestamp automatically

You can just use a TIMESTAMP field in your table with the current timestamp as a value. When a record is created it will assign the timestamp automatically.

Thanx will try that out. You go for SQlite or better Mysql? Just questioning, cause i got a project and that DB looks quite good (80%) is already well done and i need to add new stuff. Never worked with SQlite,so not sure to migrate to mysql and change my fields or go with existing SQLite…?

I don’t see any specific reason to migrate to sqlite if you have your data(and database) already setup in mysql.