CODEWITHSUNDEEP
postgresql
Joseph Freeman
Joseph Freeman

Reputation: 1784

Converting a integer to date

I am having an issue with converting an integer date (20180525) to a date in the format of YYYY-MM-DD. Is there a way to do this or should I just convert it in code (Java in this case)? Any help with this would be most appreciated. Thank you!

Upvotes: 6

Views: 40533

Answers (4)

Vasilii Suricov
Vasilii Suricov

Reputation: 954

Not correct answer is to_timestamp(timestamp_int)::date, but it's really what i was expected to find under this title/question.
Explanation that someone save date as int 20180525 shows that the world is full of incredible ideas

Upvotes: 1

phsaires
phsaires

Reputation: 2378

select ((field_int)::text)::date as field_date from your_table;

Upvotes: 1

Basil Bourque
Basil Bourque

Reputation: 338406

tl;dr

Either SQL or Java works well. For SQL, see the correct Answer by Ali.

For Java, pass a LocalDate to a PreparedStatement query.

LocalDate.parse(                      // `LocalDate` represents a date-only value without time-of-day and without time zone. Works with SQL-standard `DATE` type.
    Integer.toString( 20180525 ) ,    // Convert integer to text, for a `String` object as input.
    DateTimeFormatter.BASIC_ISO_DATE  // Specify a formatting pattern to match our input string.
)                                     // Returns a `LocalDate` object.

2018-05-25

java.time

Regarding Java mentioned in the Question, here is code using the modern java.time classes. Whether you should use SQL or Java to parse you string depends on your situation and your tastes, as either route works.

LocalDate

The LocalDate class represents a date-only value without time-of-day and without time zone.

You need to specify a formatting pattern to match your input. In this particular case, your input string is in standard ISO 8601 format. Specifically, the “basic” variant of ISO 8601 formats that minimize the use of delimiters. In java.time, you will find this particular formatting pattern is conveniently predefined for you.

String input = Integer.toString( 20180525 ) ;  // Convert integer to text for input as `String`. 
DateTimeFormatter f = DateTimeFormatter.BASIC_ISO_DATE ;
LocalDate ld = LocalDate.parse( input , f ) ;

ld.toString(): 2018-05-25

JDBC

As of JDBC 4.2 and later, you can directly exchange java.time objects with your database. Always better to use smart objects rather than dumb strings.

String sql = "SELECT event_date FROM tbl WHERE event_date = ? ; " ;
…
myPreparedStatement.setObject( 1 , ld ) ;

And retrieval.

LocalDate ld = myResultSet.getObject( … , LocalDate.class ) ;

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

Upvotes: 2

Haleemur Ali
Haleemur Ali

Reputation: 28243

if your dates are stored as integers with YYYYMMDD, you can pass it to the date function by first casting to TEXT.

SELECT date(20180525::TEXT)

otherwise, use the function to_date with a date formatter: 

SELECT to_date(20180525::text, 'YYYYMMDD')

Upvotes: 14

Related Questions