Level 75
timestamp would be following laravel conventions. Sotre UTC, convert to user for viewing, etc.
Sep 19, 2021
7
Level 1
Should I store created_at as timestamp or integer?
I am in dilemma choosing data type to use on created_at column or any date time related column.
If I am implementing a software that will be used over multi countries, which approaches will best fit and commonly used? Is there a different when store as integer over timestamp?
How the comparison worked? Converting date time to unix time and compare?
Appreciated for the feedback given. Thanks.
Level 1
@jlrdw Let's say if want to compare for the date time stored in database, and user selected date time is local date time. Are we converting it and compared with our database value? Or we converted the database column value?
Level 75
@zidance Store UTC in database, but also user needs a field (column) for their timezone. Then whenever you are working with that UTC timestamp, you convert to user timezone for display. This is usually how it's done.
I usually don't worry about all timestamps, like created at, unless customer wants to see local date time. Each use case is different.
Edit:
Of course you could store user timestamp (in their timezone) as well, it's your preference.
Level 1
@jlrdw Oh, what I mean here is something like date time picker usually use local date time. So when passed to backend, which one(user or database field) more preferable to be converted? Should be user data right?
// which to be converted
->where('created_at', '>=', $request->startDate);
Level 75
There is a big difference between a timestamp and date time or date field.
How you handle (convert) dates is up to you, but if a supervisor needs to look up an employees hired date they are going to need to see that date in their time zone they are actually in.
Don't use timestamps for a hire date. Use a date field.
Edit:
You are only in one time zone with all the users, just use that time zone all the time.
If World wide users, convert as needed.
Also read this in the mysql manual: https://dev.mysql.com/doc/refman/8.0/en/datetime.html
Level 122
use timestamp or date time DO NOT store as integer
doing so would mean you can no longer perform any comparisons using the database native date time functions such as finding all records created today or all in september for instance
Level 80
@zidance If you were meant to store dates and times as an integer then when would databases like MySQL have a date type column?
Use date columns for storing dates, and use integer columns for storing integers.
Please or to participate in this conversation.