Menu
I am guessing this is relatively simple to do, but I am unsure of the syntax. I have date and time columns that I want to combine to a timestamp column. how would I query this using a select?
373
To combine date and time column into a timestamp, you can use cast() function with concat(). select cast(concat(yourDateColumnName, ' ', yourTimeColumnName) as datetime) as anyVariableName from yourTableName; In the above concept, you will use cast() when your date and time is in string format.
Here is the SQL you can use to add the column in: ALTER TABLE `table1` ADD `lastUpdated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ; This adds a column called 'lastUpdated' with a default value of the current date/time.
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.
Or you could use the built-in TIMESTAMP(date,time) function.
So then you would do something like this say from an Orders table...
SELECT OrderNumber, TIMESTAMP(date,time) as OrderTS, SalesPersonID FROM Orders 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
