CODEWITHSUNDEEP
mysqlsqlpostgresqldatetimetimestamp
Marsellus Wallace
Marsellus Wallace

Reputation: 18611

CURRENT_TIMESTAMP in milliseconds

Is there any way to get milliseconds out of a timestamp in MySql or PostgreSql (or others just out of curiosity)?

SELECT CURRENT_TIMESTAMP
--> 2012-03-08 20:12:06.032572

Is there anything like this:

SELECT CURRENT_MILLISEC
--> 1331255526000

or the only alternative is to use the DATEDIFF from the era?

Upvotes: 96

Views: 244974

Answers (22)

Mike
Mike

Reputation: 1

You should be able to use UTC_TIME(3) instead of CURRENT_TIME(), CURRTIME(), NOW() to avoid the daylight saving time issue.

SELECT FLOOR(UNIX_TIMESTAMP(UTC_TIME(3)) * 1000);

Upvotes: 0

Igor
Igor

Reputation: 688

The main misunderstanding in MySQL with timestamps is that MySQL by default both returns and stores timestamps without a fractional part.

SELECT current_timestamp()  => 2018-01-18 12:05:34

which can be converted to seconds timestamp as

SELECT UNIX_TIMESTAMP(current_timestamp()) => 1516272429

To add fractional part:

SELECT current_timestamp(3) => 2018-01-18 12:05:58.983

which can be converted to milliseconds timestamp as

SELECT CAST( 1000*UNIX_TIMESTAMP(current_timestamp(3)) AS UNSIGNED INTEGER) ts => 1516272274786

There are few tricks with storing in tables. If your table was created like

    CREATE TABLE `ts_test_table` (
      `id` int(1) NOT NULL,
      `not_fractional_timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

than MySQL will NOT store fractional part within it:

    id, not_fractional_timestamp
    1,  2018-01-18 11:35:12

If you want to add fractional part into your table, you need to create your table in another way:

    CREATE TABLE `ts_test_table2` (
      `id` int(1) NOT NULL,
      `some_data` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
      `fractional_timestamp` timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

that leads to required result:

    id, some_data, fractional_timestamp
    1,  8,         2018-01-18 11:45:40.811

current_timestamp() function is allowed to receive value up to 6, but I've found out (at least in my installed MySQL 5.7.11 version on Windows) that fraction precision 6 leads to the same constant value of 3 digits at the tail, in my case 688

    id, some_data, fractional_timestamp
    1,  2,         2018-01-18 12:01:54.167688
    2,  4,         2018-01-18 12:01:58.893688

That means that really usable timestamp precision of MySQL is platform-dependent:

  • on Windows: 3
  • on Linux: 6

Upvotes: 35

Rémi Becheras
Rémi Becheras

Reputation: 15232

None of these responses really solve the problem in postgreSQL, i.e :

getting the unix timestamp of a date field in milliseconds

I had the same issue and tested the different previous responses without satisfying result.

Finally, I found a really simple way, probably the simplest :

SELECT ROUND(EXTRACT (EPOCH FROM <date_column>::timestamp)::float*1000) as unix_tms
FROM <table>

namely :

  • We extract the pgSQL EPOCH, i.e. unix timestamp in floatting seconds from our column casted in timestamp prudence (in some complexe queries, pgSQL could trow an error if this cast isn't explicit. See )
  • then we cast it in float and multiply it by 1000 to get the value in milliseconds
  • then we round it to drop the fractional part

Upvotes: 4

CompEng88
CompEng88

Reputation: 1532

Poster is asking for an integer value of MS since Epoch, not a time or S since Epoch.

For that, you need to use NOW(3) which gives you time in fractional seconds to 3 decimal places (ie MS precision): 2020-02-13 16:30:18.236

Then UNIX_TIMESTAMP(NOW(3)) to get the time to fractional seconds since epoc: 1581611418.236

Finally, FLOOR(UNIX_TIMESTAMP(NOW(3))*1000) to get it to a nice round integer, for ms since epoc: 1581611418236

Make it a MySQL Function:

CREATE FUNCTION UNIX_MS() RETURNS BIGINT DETERMINISTIC
BEGIN
    RETURN FLOOR(UNIX_TIMESTAMP(NOW(3))*1000);
END

Now run SELECT UNIX_MS();

Note: this was all copied by hand so if there are mistakes feel free to fix ;)

Upvotes: 24

Malvin Lok
Malvin Lok

Reputation: 642

Mysql:

SELECT REPLACE(unix_timestamp(current_timestamp(3)),'.','');

Upvotes: 0

Peter
Peter

Reputation: 338

Postgres: SELECT (extract(epoch from now())*1000)::bigint;

Upvotes: 2

аlex
аlex

Reputation: 5698

For mysql:

SELECT (UNIX_TIMESTAMP() * 1000) AS unix_now_in_microseconds; --- 1600698677000

Upvotes: 0

Bazardshoxer
Bazardshoxer

Reputation: 645

For everyone here, just listen / read the comments of Doin very good! The UNIX_TIMESTAMP() function will, when a datatime-string is given, contact a local time, based on the timezone of the MySQL Connection or the server, to a unix timestamp. When in a different timezone and dealing with daylight savings, one hour per year, this will go wrong!

For example, in the Netherlands, the last Sunday of October, a second after reaching 02:59:59 for the first time, the time will be set back to 02:00:00 again. When using the NOW(), CURTIME() or SYSDATE()-functions from MySQL and passing it to the UNIX_TIMESTAMP() function, the timestamps will be wrong for a whole our.

For example, on Satudray 27th of October 2018, the time and timestamps went like this:

Local time                        |  UTC Time                 |  Timestamp   |  Timestamp using MYSQL's UNIX_TIMESTAMP(NOW(4))
----------------------------------+---------------------------+--------------+-----------------------------------------------------
2018-10-27 01:59:59 CET (+02:00)  |  2018-10-26 23:59:59 UTC  |  1540598399  |  1540598399
2018-10-27 02:00:00 CET (+02:00)  |  2018-10-27 00:00:00 UTC  |  1540598400  |  1540598400 + 1 second
2018-10-27 02:59:59 CET (+02:00)  |  2018-10-27 00:59:59 UTC  |  1540601999  |  1540601999 
2018-10-27 03:00:00 CET (+02:00)  |  2018-10-27 01:00:00 UTC  |  1540602000  |  1540602000 + 1 second
2018-10-27 03:59:59 CET (+02:00)  |  2018-10-27 01:59:59 UTC  |  1540605599  |  1540605599
2018-10-27 04:00:00 CET (+02:00)  |  2018-10-27 02:00:00 UTC  |  1540605600  |  1540605600 + 1 second

But on Sunday 27th of October 2019, when we've adjusted the clock one hour. Because the local time, doensn't include information whether it's +02:00 or +01:00, converting the time 02:00:00 the first time and the second time, both give the same timestamp (from the second 02:00:00) when using MYSQL's UNIX_TIMESTAMP(NOW(4)) function. So, when checking the timestamps in the database, it did this: +1 +1 +3601 +1 +1 ... +1 +1 -3599 +1 +1 etc.

Local time                        |  UTC Time                 |  Timestamp   |  Timestamp using MYSQL's UNIX_TIMESTAMP(NOW(4))
----------------------------------+---------------------------+--------------+-----------------------------------------------------
2019-10-27 01:59:59 CET (+02:00)  |  2019-10-26 23:59:59 UTC  |  1572134399  |  1572134399
2019-10-27 02:00:00 CET (+02:00)  |  2019-10-27 00:00:00 UTC  |  1572134400  |  1572138000 + 3601 seconds
2019-10-27 02:59:59 CET (+02:00)  |  2019-10-27 00:59:59 UTC  |  1572137999  |  1572141599
2019-10-27 02:00:00 CET (+01:00)  |  2019-10-27 01:00:00 UTC  |  1572138000  |  1572138000 - 3599 seconds
2019-10-27 02:59:59 CET (+01:00)  |  2019-10-27 01:59:59 UTC  |  1572141599  |  1572141599
2019-10-27 03:00:00 CET (+01:00)  |  2019-10-27 02:00:00 UTC  |  1572141600  |  1572141600 + 1 second

Relaying on the UNIX_TIMESTAMP()-function from MySQL when converting local times, unfortunately, is very unreliable! Instead of using SELECT UNIX_TIMESTAMP(NOW(4)), we're now using the code below, which seams to solve the issue.

SELECT ROUND(UNIX_TIMESTAMP() + (MICROSECOND(UTC_TIME(6))*0.000001), 4)

Upvotes: 0

Justin Levene
Justin Levene

Reputation: 1677

Do as follows for milliseconds:

select round(date_format(CURTIME(3), "%f")/1000)

You can get microseconds by the following:

select date_format(CURTIME(6), "%f")

Upvotes: -1

Ahmed
Ahmed

Reputation: 247

In Mysql 5.7+ you can execute 

select current_timestamp(6)

for more details

https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html

Upvotes: 18

silviu
silviu

Reputation: 189

I faced the same issue recently and I created a small github project that contains a new mysql function UNIX_TIMESTAMP_MS() that returns the current timestamp in milliseconds.

Also you can do the following :

SELECT UNIX_TIMESTAMP_MS(NOW(3)) or SELECT UNIX_TIMESTAMP_MS(DateTimeField)

The project is located here : https://github.com/silviucpp/unix_timestamp_ms

To compile you need to Just run make compile in the project root.

Then you need to only copy the shared library in the /usr/lib/mysql/plugin/ (or whatever the plugin folder is on your machine.)

After this just open a mysql console and run :

CREATE FUNCTION UNIX_TIMESTAMP_MS RETURNS INT SONAME 'unix_timestamp_ms.so';

I hope this will help, Silviu

Upvotes: -1

Chebevara
Chebevara

Reputation: 194

In PostgreSQL we use this approach:

SELECT round(EXTRACT (EPOCH FROM now())::float*1000)

Upvotes: 1

Benvorth
Benvorth

Reputation: 7732

In MariaDB you can use

SELECT NOW(4);

To get milisecs. See here, too.

Upvotes: 1

Yaniv
Yaniv

Reputation: 572

I felt the need to continue to refine, so in MySQL:

Current timestamp in milliseconds:

floor(unix_timestamp(current_timestamp(3)) * 1000)

Timestamp in milliseconds from given datetime(3):

floor(unix_timestamp("2015-04-27 15:14:55.692") * 1000)

Convert timestamp in milliseconds to datetime(3):

from_unixtime(1430146422456 / 1000)

Convert datetime(3) to timestamp in milliseconds:

floor(unix_timestamp("2015-04-27 14:53:42.456") * 1000)

Upvotes: 0

Claudio Holanda
Claudio Holanda

Reputation: 2576

For MySQL (5.6+) you can do this:

SELECT ROUND(UNIX_TIMESTAMP(CURTIME(4)) * 1000)

Which will return (e.g.):

1420998416685 --milliseconds

Upvotes: 76

Yuval
Yuval

Reputation: 381

Easiest way I found to receive current time in milliseconds in MySql:

SELECT (UNIX_TIMESTAMP(NOW(3)) * 1000)

Since MySql 5.6.

Upvotes: 7

jbaylina
jbaylina

Reputation: 4738

In mysql, it is possible to use the uuid function to extract milliseconds.

select conv( 
            concat(
                   substring(uid,16,3), 
                   substring(uid,10,4), 
                   substring(uid,1,8))
                   ,16,10) 
            div 10000 
            - (141427 * 24 * 60 * 60 * 1000) as current_mills
from (select uuid() uid) as alias;

Result:

+---------------+
| current_mills |
+---------------+
| 1410954031133 |
+---------------+

It also works in older mysql versions!

Thank you to this page: http://rpbouman.blogspot.com.es/2014/06/mysql-extracting-timstamp-and-mac.html

Upvotes: 39

Walter Mundt
Walter Mundt

Reputation: 25281

Here's an expression that works for MariaDB and MySQL >= 5.6:

SELECT (UNIX_TIMESTAMP(NOW()) * 1000000 + MICROSECOND(NOW(6))) AS unix_now_in_microseconds;

This relies on the fact that NOW() always returns the same time throughout a query; it's possible that a plain UNIX_TIMESTAMP() would work as well, I'm not sure based on the documentation. It also requires MySQL >= 5.6 for the new precision argument for NOW() function (MariaDB works too).

Upvotes: 4

Billy Moon
Billy Moon

Reputation: 58619

To get the Unix timestamp in seconds in MySQL:

select UNIX_TIMESTAMP();

Details: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

Not tested PostgreSQL, but according to this site it should work: http://www.raditha.com/postgres/timestamp.php

select round( date_part( 'epoch', now() ) );

Upvotes: 49

TanuAD
TanuAD

Reputation: 784

Use:

Select curtime(4);

This will give you milliseconds.

Upvotes: 7

Falco
Falco

Reputation: 162

The correct way of extracting miliseconds from a timestamp value on PostgreSQL accordingly to current documentation is:

SELECT date_part('milliseconds', current_timestamp);

--OR

SELECT EXTRACT(MILLISECONDS FROM current_timestamp);

with returns: The seconds field, including fractional parts, multiplied by 1000. Note that this includes full seconds.

Upvotes: 6

aleroot
aleroot

Reputation: 72696

In PostgreSQL you can use : 

SELECT extract(epoch from now());

on MySQL :

SELECT unix_timestamp(now());

Upvotes: 3

Related Questions