Save date in unix format in Mysql

Question

Save date in unix format in Mysql

Navigation

#1 by (3 votes)

2

I need to store records with field in date and time in Unix format by default in a mysql table, is it possible to do that? Can you create a table with this field by default?

mysql timestamp unix

asked by Alejandro 01.09.2018 в 02:52

source

1 answer

Format for currency (MXN) DataFormatString How to center horizontally and spaced several elements within a div?

score 3 · Answer 1

It can be saved, but clarifying the following

The type of data that will be thrown when being in UNIX format is of type INT

The function UNIX_TIMESTAMP() should be used

EXAMPLE

This is the table where I will save the information including the date in UNIX format

CREATE TABLE data(
 name VARCHAR(20),
 fecha INT
);

At the time of inserting, the type of data to work should be, for example, "1997-10-04 22:23:00"

At the time of insertion, it should be left like this

INSERT INTO data
VALUES
("alfred", UNIX_TIMESTAMP(NOW()));

When you check the date, it should be displayed as following visualzia

SELECT * FROM data;

name    fecha
alfred  1535763496

If after that you need to recover the date but pass it from the UNIX format to a format like the one mentioned above, you must do this

SELECT name, FROM_UNIXTIME(fecha) FROM data;

And the result you should see is the following

name    FROM_UNIXTIME(fecha)
alfred  2018-09-01 02:02:39

Here I leave you information from official sources about the 2 functions used

1.- link

2.- link

I leave you in this link the functional example of everything that you I explain above