Menu
I have exported my database to a CSV file and the timestamp now liiks like this:
1384204028
How can I convert it to the typical format, for example 2013-01-19 03:14:07 ?
760
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME, which is stored “as is”.) By default, the current time zone for each connection is the server's time.
The basic syntax of “timestamp” data type in SQL is as follows : Timestamp 'date_expression time_expression'; A valid timestamp data expression consists of a date and a time, followed by an optional BC or AD.
SQL Date Data Types MySQL comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD. DATETIME - format: YYYY-MM-DD HH:MI:SS. TIMESTAMP - format: YYYY-MM-DD HH:MI:SS.
select to_char(sysdate, 'YYYY-MM-DD') from dual; To get this format by default, set it in your session's NLS_DATE_FORMAT parameter: alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD'; You can also set the NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT .
Use FROM_UNIXTIME()
SELECT FROM_UNIXTIME(1384204028); or (equivalent but with parameter to control the format):
SELECT FROM_UNIXTIME(1384204028, '%Y-%m-%d %H:%i:%s'); 
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
