Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting timestamp using MySQL

How can I get the current timestamp using a mysql query?

like image 588
bsytKorbi Avatar asked Dec 10 '10 16:12

bsytKorbi


People also ask

Does MySQL have TIMESTAMP?

MySQL retrieves and displays DATETIME values in ' YYYY-MM-DD hh:mm:ss ' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59' . The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

How do I display a TIMESTAMP in SQL?

The CURRENT_TIMESTAMP function returns the current date and time, in a 'YYYY-MM-DD hh:mm:ss.

How is TIMESTAMP stored in MySQL?

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.

How do I create a TIMESTAMP in SQL?

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.


2 Answers

Depends on which kind you're looking for.

The current integer Unix Timestamp (1350517005) can be retrieved like so:

SELECT UNIX_TIMESTAMP(); 

MySQL often displays timestamps as date/time strings. To get one of those, these are your basic options (from the MySQL Date & Time reference):

SELECT CURRENT_TIMESTAMP; SELECT CURRENT_TIMESTAMP(); SELECT NOW(); 
like image 133
Brad Koch Avatar answered Sep 29 '22 15:09

Brad Koch


CURRENT_TIMESTAMP is standard SQL and works on SQL server, Oracle, MySQL, etc. You should try to keep to the standard as much as you can.

like image 43
Alex Avatar answered Sep 29 '22 15:09

Alex