Is there advantage of using Integer in table column created_at?

[jemz]

Hi, can I ask I have some existing table designed by the previous DEV. I found out that the table has column created_at and he uses Integer . Is there an advantage of using this integer instead of using DATETIME datatype ?

Thank you in advance.

[DaveMaxwell]

Is it storing date type of information, or could created_at be a different type of data (like a location id or something)

If it’s truly a date time datatime, then no, there’s no advantage because the data would have to be transformed every time it’s selected which negates any possible table size differential savings.

[r937]

“negates”

[jemz]

Thank you, yest it’s storing datetime, and so there is no advantage ?

[jemz]

negates mean “ineffective” ?

[r937]

so could you give an example of one of these datetime values?

[jemz]

they store it as unixtimestamp someting like this 1257290640?

[r937]

okay, check this out

SELECT FROM_UNIXTIME(1257290640)

FROM_UNIXTIME(1257290640)
2009-11-03 18:24:00

okay, let’s say you wanted all rows for january 2019

show me your WHERE clause

[m_hutley]

(This may be of assistance in answering Rudy’s challenge…)

[jemz]

Am I right ?

SELECT DATE_FORMAT( FROM_UNIXTIME( timestamp ) , ‘%Y-%m-%d’ ) AS ‘formatdate’ FROM mytable where DATE_FORMAT(FROM_UNIXTIME( timestamp ),‘%Y-%m’) = ‘2019-01’ ;

[rpkamp]

The advantage of using timestamps over datetime is that the timestamp is an absolute point in time, whereas a datetime is not. For example, when daylight saving ends and the clock is turned back one hour, that hour between 2AM and 3AM exists twice. With datetime you can never tell the difference in which of the two something happened, but with timestamps you can, because those don’t go back one hour when daylight savings ends, they just keep running as if nothing ever happened.

Another advantage of timestamps is that you can directly convert them to any timezone you want. You just tell your programming language to create a datetime for that timestamp in timezone XYZ, whereas if you store a datetime you first need to convert back to some common timezone (probably UTC) and convert from there to the desired timezone.

The disadvantage of timestamps is that they’re not human readable. Whether or not that weighs up against the advantages points out above is up to you to decide

[r937]

@jemz

Am I right ?

what happened when you tested it? ™

[r937]

@jemz

SELECT DATE_FORMAT( FROM_UNIXTIME( timestamp ) , ‘%Y-%m-%d’ ) AS ‘formatdate’ FROM mytable where DATE_FORMAT(FROM_UNIXTIME( timestamp ),‘%Y-%m’) = ‘2019-01’ ;

your WHERE clause is not sargable because you’re applying a function to a column

it would be better written like this –

WHERE timestamp >= UNIX_TIMESTAMP('2019-01-01')
  AND timestamp  < UNIX_TIMESTAMP('2019-02-01')

[jemz]

Thank yo for the reply but can you expand mean of " sargable" ?. Is there performance issue in my query vs to yours ?

[r937]

@jemz

Thank yo for the reply but can you expand mean of " sargable " ?

sargable

[m_hutley]

@rpkamp

the timestamp is an absolute point in time, whereas a datetime is not

• in SQL.

It’s important to note that ‘Datetime’ is not a universal definition of the term; python and PHP both implement something called a ‘datetime’, but include (or can include) sufficient specificity as to actually BE an absolute point in time.

Also, I learned a new word today. Huzzah.