Timestamp

Navigation

Online convertor (http://www.raditha.com/mysql/mysql2pgsql.php)
Making up the numbers
Basic Administration
Strange characters
Links (http://www.raditha.com/mysql/links.php)
Date and Time

Migration (http://www.raditha.com/postgres/)

Judging by the number of messages posted daily on various newsgroups and mailing lists, date and time fields are one of the most confusing for developers. Mysql as usual has done their best to increase this confusion. The behaviour of date time fields even differ between different versions of that database.

In postgresql the types are simply TIMESTAMP, DATE, TIME and INTERVAL. Just to ensure that you don't get the hang of it too easily, there is a WITHOUT TIME ZONE qualifier that can be used on all but the interval field.

Mysql has two additional data types named DATETIME and YEAR thankfully it does not have the WITHOUT TIME ZONE qualifier but as we discovered with string columns, some of the six types exibit nearly identical behaviour.

Mysql will accept almost any delimiter to separate the year, month, date etc from these fields. In fact it will also accept strings without any delimiters at all provided that the string makes sense. Dates that don't make sense will usually be converted to 0 (0000-00-00 00:00:00),


Comparision of Date and Time types.
mySQL Postgresql
Type Range Description Type Range Description
datetime 1000-01-01 to 9999-12-31 Both date and time N/A N/A N/A
timestamp 1970-01-01 to 2037-12-31 Both date and time timestamp 4713 BC to 5874897 AD Both date and time
date 1000-01-01 to 9999-12-31 Date only date 4713 BC to 32767 AD Date only.
time -838:59:59 to '838:59:59 Elapsed time or time of day time 00:00:00.00 to 23:59:59.99 Time of day
year 1901 to 2155 Year N/A N/A N/A
N/A N/A N/A interval -178000000 years to 178000000 years Time intervals


So it would seem that we could use the postgresql timestamp field in place of the datetime field in mysql however mysql does not have an answer for the interval type. Though the year and time types of mysql do not have a direct counterpart in the other system, their input and output can be simulated with appropriate formatting .

Copyright © Raditha Dissanayake 2013