How to Use unix_timestamp in MySQL: Syntax and Examples

In MySQL, use the unix_timestamp() function to get the current Unix timestamp or convert a date to a Unix timestamp. You can call unix_timestamp() without arguments to get the current time in seconds since 1970-01-01 00:00:00 UTC, or pass a date string to convert it.

Syntax

The unix_timestamp() function can be used in two ways:

• unix_timestamp(): Returns the current Unix timestamp as an integer.
• unix_timestamp(date): Converts a date or datetime string to a Unix timestamp.

If the date is invalid or NULL, it returns 0.

sql

unix_timestamp()
unix_timestamp(date)

Example

This example shows how to get the current Unix timestamp and convert a specific date to a Unix timestamp.

sql

SELECT unix_timestamp() AS current_timestamp;
SELECT unix_timestamp('2024-06-01 12:00:00') AS converted_timestamp;

Output

current_timestamp
----------------
1717176000

converted_timestamp
-------------------
1717176000

Common Pitfalls

Common mistakes include:

• Passing an invalid date string returns 0 instead of an error.
• Using unix_timestamp() without arguments returns the current time, not a conversion.
• Unix timestamps are in seconds, not milliseconds.

Example of wrong and right usage:

sql

SELECT unix_timestamp('invalid-date') AS wrong_usage;
SELECT unix_timestamp('2024-06-01 12:00:00') AS right_usage;

Output

wrong_usage
-----------
0

right_usage
-----------
1717176000

Quick Reference

Usage	Description	Example
unix_timestamp()	Returns current Unix timestamp	SELECT unix_timestamp();
unix_timestamp(date)	Converts date to Unix timestamp	SELECT unix_timestamp('2024-06-01 12:00:00');
FROM_UNIXTIME(timestamp)	Converts Unix timestamp back to date	SELECT FROM_UNIXTIME(1717176000);

Key Takeaways

Use unix_timestamp() without arguments to get the current Unix timestamp in seconds.

Pass a valid date string to unix_timestamp(date) to convert it to a Unix timestamp.

Invalid date strings return 0 instead of an error.

Unix timestamps count seconds since 1970-01-01 00:00:00 UTC.

Use FROM_UNIXTIME() to convert Unix timestamps back to readable dates.