#current_timestamp

Spark – How to get current date & timestamp

Spark SQL provides current_date() and current_timestamp() functions which returns current system date without timestamp and current system data with timestamp respectively, Let’s see how to get these with Scala and Pyspark examples.

current_date() – function return current system date without time in Spark DateType format “yyyy-MM-dd”

current_timestamp() – function returns current system…

Trying to have app use database time for everything. Sequel gem docs make it straight forward, of course not in this app. Looking to pull the CURRENT_TIMESTAMP value out of Postgres and use it as a time object in Rails. The code:

Sequel::Model.db.get(Sequel.CURRENT_TIMESTAMP)

This returns a Time class object, and you can do all the normal stuff to it (ie. .to_f, .to_datetime, etc) that you’d expect work.

A fairly common requirement for database tables is to have a created and last modified timestamp for each record. This seems to be yet another spot where something fairly straightforward requires you to take the scenic route to implement. Ordinarily, the create table statement would look like:

CREATE TABLE `mydb`.`mytable` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `myfield` VARCHAR(255) NOT NULL , `modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE NOW() , `created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE = MyISAM;

And that’s when MySQL threw me a nice

Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause.

Upon digging a little further, I found this little nugget in the documentation:

For one TIMESTAMP column in a table, you can assign the current timestamp as the default value and the auto-update value. It is possible to have the current timestamp be the default value for initializing the column, for the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

Well isn’t that just peachy.

The Solution

To overcome this hurdle, I employed the use of a trigger for the created timestamp and decided to leave the modified as is. The create table statement then turned into:

CREATE TABLE `mydb`.`mytable` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `myfield` VARCHAR(255) NOT NULL , `modified` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE NOW() , `created` TIMESTAMP NULL, PRIMARY KEY (`id`) ) ENGINE = MyISAM;

And used the following trigger to process the created timestamp -

USE `mydb`; DELIMITER $$ CREATE TRIGGER trigger_mytable_before_insert BEFORE INSERT ON `mytable` FOR EACH ROW BEGIN SET NEW.created = NOW(); END; $$