-1

I know there some functions for date/time and number formatting but is there a way to store and read date and number columns with a custom format?

Ex.:

In my country DD/MM/YYYY is a common date format but if I want to store it in a mysql or postgres database I must store it as YYYY-MM-DD.

I've read about some configuration directives in postgresql that permits storing dates / numbers in specifc formats.

I think it would be a lot faster to store and read these values without need of conversion routines.

Carlos
  • 53
  • 5
  • 1
    What RDMS are you actually using? MySQL or PostgreSQL? – John Conde Oct 23 '14 at 17:15
  • Both. Its a multidatabase project I just started... – Carlos Oct 23 '14 at 17:20
  • 1
    Please handle this at the outer edges of your application. You'll have a much better time if you always speak to the database using sensible ISO 8601 formats. Time and date handling is complicated enough without mashing ambiguous and locale-specific formats into the mix. – mu is too short Oct 23 '14 at 17:43
  • "*that permits storing dates / numbers in specifc formats*" - DATEs do ***NOT*** have "a format". A column defined as `DATE` stores the date value in a binary way (e.g. a numeric value). There is ***NO*** "format" attached to that. Any format you see for those columns is applied by the application (e.g. the SQL client) that _displays_ the data in those columns. Store your dates in columns defined as `DATE` and do the formatting in your application (or use `to_char()` to a apply a format in a SQL statement). –  Oct 23 '14 at 18:45

2 Answers2

1

I highly recommend against changing the date format but if you want to do it it's a global variable. Here's an example for MySQL.

mysql> show global variables like '%date%';
+-----------------------------------------+-------------------+
| Variable_name                           | Value             |
+-----------------------------------------+-------------------+
| binlog_direct_non_transactional_updates | OFF               |
| date_format                             | %Y-%m-%d          |
| datetime_format                         | %Y-%m-%d %H:%i:%s |
| log_slave_updates                       | OFF               |
| low_priority_updates                    | OFF               |
| query_cache_wlock_invalidate            | OFF               |
| sql_safe_updates                        | OFF               |
+-----------------------------------------+-------------------+
7 rows in set (0.00 sec)

so you'll want to set date_format and datetime_format to the desired format. i.e.

This is for the server 

set global variable date_format = '%d-%m-%Y'; 
set global variable datetime_format = '%d-%m-%Y %H:%i:%s';

This is on the client

set date_format = '%d-%m-%Y'; 
set datetime_format = '%d-%m-%Y %H:%i:%s';
jbrahy
  • 4,228
  • 1
  • 42
  • 54
  • Hi. Thanks for your answer. Why youl'd say you "highly recommend" changing these variables? – Carlos Oct 23 '14 at 17:20
  • I wouldn't expect every developer to write code to check date format in their applications so if you're writing the code yourself then you might be ok, if you're working with a third party application you might get undesired results. Sticking with the system standard is usually safest. – jbrahy Oct 23 '14 at 17:25
0

Maybe this link will help you:

How do I alter the date format in Postgres?

or this one:

How to convert date strings to timestamp without knowing the date format

Community
  • 1
  • 1
madtyn
  • 1,469
  • 27
  • 55