Inserting TimeStamp into MySql Database via php

Question

This question might be stupid but i am really curious on what is the recommended way. I am making demo rest api for my iOS app. I am iOS programmer and getting into learning php to write my own quick demo api.

On one of my table i have column of type TIMESTAMP. I am sending unix timestamp value to my api that will insert that value into that column.

I checked database and default timestamp value format is stored like

2015-04-15 16:48:25

and unix timestamp is like

1430233486

My API receives unix timestamp from my iOS app. Now my question is do i have to convert this timestamp that my app sends to the format that mysql saves ? I need to save data in single format. One more info: Sometimes if timestamp is not sent by app, then mysql inserts the current timestamp itself and this current timestamp is in format 2015-04-15 16:48:25 again.

My preference is to save in traditional unix timestamp format. Is there any settings/query that if mysql decides to store current timestamp in column than its always in 1430233486 format ?

Note: My assumption is that TIMESTAMP type in mysql means unix TIMESTAMP. There are DATETIME type too in mysql. I may be wrong with my assumption as i can simply use INT type for storing my unix timestamps but i like the feature of mysql inserting current timestamp as default without business logic code if i don't provide any timestamp from app.

Ofir Baruch · Accepted Answer · 2015-04-28 15:31:38Z

As far as I know, There's no a dedicated field's type for unix timestamps in mysql. Unix timestamps are actually signed 32bit integer, therefore you can use the INT type to store them.

Please be aware to the range of the INT type field (https://msdn.microsoft.com/en-us/library/ms187745.aspx). I believe that INT would work for you, but just in case you're dealing with off-the-range numbers, you might need to use BIGINT.

In my opinion, you should store the unix timestamp rather than the formatted date/time option since it's easy to manipulate, for calculations or reformatting.

Was about to suggest this. If your API is receiving timestamps from your iOS app in the form of INT, might as well store it the same way in the database.

Germanaz0 · Accepted Answer · 2015-04-28 15:34:45Z

I recommend you to don't use 2 different types of data into the same column of database, is not data consistent.

I recommend you to do a function or method that compares if the value is a string or a number and then convert it to a datetime or timestamp.

Like for example:

function convertTime($value) { if (is_numeric($value)) { return date("Y-m-d H:i:s", $value); } return $value; }

Or to convert from date to unix timestamp:

function convertTime($value) { if (is_string($value)) { return strtotime($value); } return $value; }

and use that function to everywhere.

Collectives™ on Stack Overflow

Inserting TimeStamp into MySql Database via php

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related