CONVERT THE TIMESTAMP IN THE MYSQL DATABASE TO TIME 100

Use a conversion function, :from unixtime, to convert the timestamp to a common time format.

select from unixtime (the name of the field to be converted) from the table name;

Do you have a lot of data?

If it's not big, just change the structure of the table below and paste it.

I'm using a sqlyog database client. Convert long time to date format via from unixtime. The specific usage is as follows:

selectfrom_unixtime(1388776825)；Just execute it.

Create a new field with the date type. The data is then read out and converted into a new field by the program.

MySQL database how to slow down the liquid sample to convert the long time and the date of the Pi Pai to create a new field, with the date type. The data is then read out and converted into a new field by the program.

The database doesn't have this conversion, and even if you convert your number, it will be considered a year. I also get to deal with this number in the business, and I don't regret that your number represents seconds or how much.

MySQL converts timestamps into common time formats.

In MySQL, the storage type of a time field is int(11), and how to convert it into a character type, for example, if it is stored as 13270655222, it needs to be converted to the form yyyy -mm-dd.

Use the from unixtime function as follows:

from_unixtime(unix_timestamp,format)

Returns a string representing a UNIX timestamp, formatted according to the formatted string. format can contain the same modifier as the entries listed by the date format() function.

Format the date value based on the format string. The following modifiers can be used in the format string: %m Month Name (january......december)

w Name of the week (sunday......saturday)

d Date of month with English prefix (1st, 2nd, 3rd, etc.) ）

y year, number, 4 digits.

y year, number, 2 digits.

a Abbreviated name of the week (sun......sat)

d Number of days in the month, number(00......31)

e Number of days in the month, Number (0......31)

m month, digital (01......12)

c Month, Number (1......12)

b Abbreviated month name (jan......dec)

j Number of days in a year (001......366)

h hours (00......23)

k hours (0......23)

h hour (01......12)

i hour (01......12)

l hours (1 ......12)

i minute, digital (00......59)

R time, 12 hours (hh:mm:ss [ap]m).

t-time, 24 hours (hh:mm:ss).

s seconds (00......59)

s seconds (00......59)

p am or pm

w Number of days in a week (0=sunday......6=saturday ）

u week (0......52), here Sunday is the first day of the week.

u week (0......52), Here Monday is the first day of the week.

A text "%".

All other characters are copied into the result without explanation.

For example: select from unixtime(1234567890,'%y-%m-%d %h:%i:%s')

You can specify the format yourself.

Different databases, different programming languages, and different ways of dealing with timestamps are also different.

1. The method of converting timestamps into ordinary time in commonly used databases and programming languages

2. Take SQL Server as an example.

Convert timestamp: 1500112590 to normal time select dateadd(s,1500112590,'1970-01-01 00:00:00')

select date_format('2013-03-09','%y-%m-%d');

select date_format('2013-03-09','%y-%m-%d');

select str_to_date('2013-03-09','%y-%m-%d');

select str_to_date('2012-10-11 16:42:30','%y-%m-%d %h:%i:%s');

w Name of the week (sunday......saturday)

d Date of month with English prefix (1st, 2nd, 3rd, etc.) ）

y year, number, 4 digits.

y year, number, 2 digits.

a Abbreviated name of the week (sun......sat)

d Number of days in the month, number(00......31)

e Number of days in the month, Number (0......31)

m month, digital (01......12)

c Month, Number (1......12)

b Abbreviated month name (jan......dec)

j Number of days in a year (001......366)

h hours (00......23)

k hours (0......23)

h hour (01......12)

i hour (01......12)

l hours (1 ......12)

i minute, digital (00......59)

R time, 12 hours (hh:mm:ss [ap]m).

t-time, 24 hours (hh:mm:ss).

s seconds (00......59)

s seconds (00......59)

p am or pm

w Number of days in a week (0=sunday......6=saturday ）

u week (0......52), here Sunday is the first day of the week.

u week (0......52), Here Monday is the first day of the week.

A text "%".

Use str to date

The following is an example: mysql> select

str_to_date('2010~10~22 ',->'%y~%m~%d %k.%i.%s' ) a;

--a |+2010-10-22 20:55:09 |+1 row in set ( sec)

Note: %w Day Name (sunday......saturday)%d The date of the month (1st, 2nd, 3rd, etc.) with English prefixes. y year, number, 4 digits.

y year, number, 2 digits.

a Abbreviated name of the week (sun......sat)

d Number of days in the month, number(00......31)%e number of days in the month, digit (0......31)

m month, digital (01......12)

c Month, Number (1......12)

b Abbreviated month name (jan......dec)

j Number of days in a year (001......366)

h hours (00......23)

k hours (0......23)

h hour (01......12)

i hour (01......12)

l hours (1 ......12)

i minute, digital (00......59)

R time, 12 hours (hh:mm:ss [ap]m)%t time, 24 hours (hh:mm:ss)%s seconds (00......59)

s seconds (00......59)

p am or pm

w Number of days in a week (0=sunday......6=saturday ）

u week (0......52), where Sunday is the first day of the week%u Week(0......52), here Monday is the first day of the week %% a text "%".

All other characters are not explained.

It is good to use int directly, after all, timestamps can be converted into time format with time functions such as from unixtime, int is a little simpler to calculate than datetime, and it is also a little faster than datetime if you feel that the amount of data is large.

The company's database knows that all the data tables built about the time are intint is simpler than datetime, and if the amount of data is large, intt is a little faster than datetime.

A timestamp, a complete, verifiable block of data that represents a piece of data that existed before a specific time, is usually a sequence of characters that identifies the time at a certain point in time.

Digital signature technology is used to generate next-generation data, and the signature object includes information such as original file information, signature parameters, and signature time. It is widely used in intellectual property protection, contract signing, financial accounting, electronic bidding, trading and other fields.

int is easier to calculate than datetime, and int is a little faster to store than datetime if the amount of data is large.

A timestamp, a complete, verifiable piece of data that existed before a certain time, is usually a sequence of characters that uniquely identifies the time at a certain moment.

The data generated by digital signature technology includes the original file information, signature parameters, signature time and other information. It is widely used in intellectual property protection, contract signing, financial accounting, electronic bidding, trading, etc.

We use int, after all, timestamps can be converted into time formats with time functions such as from unixtime(), and I personally feel that int is simpler than datetime calculations, and there is a feeling that if the amount of data is large, int should be faster than datetime, our company's database is a big bull, and all the data tables built about time are int

It doesn't matter, I really don't think there's any need to dwell on this. It's still very troublesome for you to use int storage, and the business layer has to be converted many times. Just use timestamp directly, our hundreds of millions of levels have been tested.

There is no pressure to query various time dimensions. Our company has 100 million+ users. The daily order volume is 30 million+.

That's what it does.